Max Date

How do i find out the max date of a particular month in a table.

I have a records in the table like this

DATE
--
01 Jan 2004
02 Jan 2004
05 Jan 2004
07 Jan 2004
04 Feb 2004
06 Feb 2004
19 Feb 2004
04 Mar 2004
28 Mar 2004
03 Apr 2004
05 Apr 2004

My output should be something like this
OUTPUT
---
07 Jan 2004
06 Feb 2004
28 Mar 2004
05 Apr 2004

I want a SQL statement that can do this for me

Thanks
ShankarHehe, just answered you on SQLServerCentral :)

select Max(datepart(dd,datecolumn)) as Day,datepart(mm,datecolumn) as month, datepart(yy,datecolumn) as Year
from table
group by datepart(mm,datecolumn),datepart(yy,datecolumn)

HTH|||thanks for your help,

i got a kind off tricky requirement here.

the query that i write has to return me 6 records always.

The first record that will be displayed will be the initial record that is created. The last record in these 6 records will be the latest record that is created. so now my question is i want to retrieve the remaining 4 records.

my conditions are like this.

i want to display the maximum date of a particular month in the 4 records. now if this condition does not give me 4 records, then i need to
pick up the current month's record also and make sure the count is equal to 4. What if i have only one record in the current month.
then i need to go to current - 1 month and pick up that record.

I need to keep doing this until i get the 4 records that i want.

Let me give u the example for this.

Lets take the example that i have given previously.

DATE
--
01 Jan 2004
02 Jan 2004
05 Jan 2004
07 Jan 2004
04 Feb 2004
06 Feb 2004
19 Feb 2004
04 Mar 2004
28 Mar 2004
03 Apr 2004
05 Apr 2004

My output should be something like this

01 Jan 2004 --> First Record that is created
07 Jan 2004 --> Max of Jan
06 Feb 2004 --> Max of Feb
28 Mar 2004 --> Max of March
03 Apr 2004 --> I choose this record becoz the count for max months
is not equal to 4 and hence to make it 4 , i add this
05 Apr 2004 --> The most recent record

Hope this helps in understanding the requirement

Thanks
Shankar

Max + 1 records in a table....

New bie, I'm using max+1 in select query to get the maximum no of rowz in a
table. Thru this i'm generating client ID. For this i used to insert atleast
one row in a table while quering . Now i have to generate 1 as for first
inserting row. then subsequent rows as max+1.
--
Mahe~SELECT IsNull(max(clientId),0)+1
FROM ...
But WHY dont you use IDENTITY instead?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"mahesh" <mfcmahesh@.hotmail.com> wrote in message
news:uXaHWl8GFHA.3244@.TK2MSFTNGP09.phx.gbl...
> New bie, I'm using max+1 in select query to get the maximum no of rowz in
> a
> table. Thru this i'm generating client ID. For this i used to insert
> atleast
> one row in a table while quering . Now i have to generate 1 as for first
> inserting row. then subsequent rows as max+1.
> --
> Mahe~
>|||Identity ?
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:utC2c38GFHA.3244@.TK2MSFTNGP09.phx.gbl...
> SELECT IsNull(max(clientId),0)+1
> FROM ...
> But WHY dont you use IDENTITY instead?
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "mahesh" <mfcmahesh@.hotmail.com> wrote in message
> news:uXaHWl8GFHA.3244@.TK2MSFTNGP09.phx.gbl...
in
>|||http://msdn.microsoft.com/library/d...br />
3iex.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"mahesh" <mfcmahesh@.hotmail.com> wrote in message
news:eMSnjB9GFHA.3440@.TK2MSFTNGP10.phx.gbl...
> Identity ?
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:utC2c38GFHA.3244@.TK2MSFTNGP09.phx.gbl...
> in
>|||Thkz Thomaz, if u dont mind,where i can find more example for sql server
related concepts and queries..
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:#adBTH9GFHA.4060@.TK2MSFTNGP14.phx.gbl...
>
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
ia-iz_3iex.asp
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "mahesh" <mfcmahesh@.hotmail.com> wrote in message
> news:eMSnjB9GFHA.3440@.TK2MSFTNGP10.phx.gbl...
rowz
>|||Roji posted a link to Books Online, which should also be available on
your PC if you have SQL Server installed. Books Online is the first
place to look for any SQL Server related info.
More resources here:
http://www.microsoft.com/sql/commun...communities.asp
http://www.aspfaq.com/2423
David Portas
SQL Server MVP
--

Matrix with fixed column values (months 1-12)

I have a matrix that shows sales per year (rows) and month (columns).
If the data being used contains no records for a particular month for any
year (e.g. November), that column (i.e. column 11) is completely missing
from the matrix.
Can the matrix be configured to always show a given set of columns, even if
there is no underlying data?
ThanksHi Laurence,
If you don't have any data rows for a certain month, then it will not show
up in the matrix grouping. To ensure that certain groups/data values are
always present, you will need an outer join in your dataset query e.g. with
a simple table that just has 12 rows with one column and values from 1 to
12.
Details on how to use outer joins are available here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
HTH,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
> I have a matrix that shows sales per year (rows) and month (columns).
> If the data being used contains no records for a particular month for any
> year (e.g. November), that column (i.e. column 11) is completely missing
> from the matrix.
> Can the matrix be configured to always show a given set of columns, even
if
> there is no underlying data?
> Thanks
>|||OK thanks, I know how to do that.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uWTPFZyuEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi Laurence,
> If you don't have any data rows for a certain month, then it will not show
> up in the matrix grouping. To ensure that certain groups/data values are
> always present, you will need an outer join in your dataset query e.g.
> with
> a simple table that just has 12 rows with one column and values from 1 to
> 12.
> Details on how to use outer joins are available here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
> http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
> HTH,
> Robert
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
>> I have a matrix that shows sales per year (rows) and month (columns).
>> If the data being used contains no records for a particular month for any
>> year (e.g. November), that column (i.e. column 11) is completely missing
>> from the matrix.
>> Can the matrix be configured to always show a given set of columns, even
> if
>> there is no underlying data?
>> Thanks
>>
>

Matrix Reports Filter

I am trying to have a matrix report filter on records that had sales in a prior month but no sales in the current month. Basically looking for accounts we “lost”

For example:

Name Invoice Date Quantity

Joe 4/1/2007 7

John 5/1/2007 13

John 4/1/2007 7

Tim 6/12/2007 6

The matrix report pulls back

Name Month

April May June

Joe 7

Tim 7 13

John 6

I am trying to return only names that had quantity in May and don't in June.

I would like it only to return Tim’s record because we lost his business in June.

Any ideas?

I think in your example you switched Tim and John between the query results and matrix.

Anyway, the best place to do this is in your query. I suspect you'll be running this report based on a "Month" parameter.

So in pseudo code you would write your query like

Code Snippet

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month - 1 month

AND account NOT IN

(

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month

)

Matrix Cross-tab Report Not Counting Records

I am new to Reporting Services and cross-tab reporting in general. I
need to create a basic report that has dates across the top and reps
down the left. The data I would like to see reflected is the number of
entries created in the table by rep by day with cross sums. What I am
getting is a report that has a column for every entry with the date
repeated over and over and no totals, only 1 in each column.
Visually I need to see:
Day 1 Day 2 Day 3 Total
__________________________________
Rep1 4 10 1 15
Rep2 2 4 3 9
Total 6 14 4 24
What I am getting is a report where Day 1 would be repeated 6 times
with an entry of 1.
Can someone point me in the right direction?I would like to add that my table uses a datetime field that has a
unique value for each record. I do need a specific timestamp.
Probably part of my problem, but how do I resolve it?
zanecolvin@.gmail.com wrote:
> I am new to Reporting Services and cross-tab reporting in general. I
> need to create a basic report that has dates across the top and reps
> down the left. The data I would like to see reflected is the number of
> entries created in the table by rep by day with cross sums. What I am
> getting is a report that has a column for every entry with the date
> repeated over and over and no totals, only 1 in each column.
> Visually I need to see:
> Day 1 Day 2 Day 3 Total
> __________________________________
> Rep1 4 10 1 15
> Rep2 2 4 3 9
> Total 6 14 4 24
> What I am getting is a report where Day 1 would be repeated 6 times
> with an entry of 1.
> Can someone point me in the right direction?|||Solved my own problem. Created a view converting data type from and
back to datetime to get 12:00 time on all entries. Used that for the
report.
zanecolvin@.gmail.com wrote:
> I would like to add that my table uses a datetime field that has a
> unique value for each record. I do need a specific timestamp.
> Probably part of my problem, but how do I resolve it?
>

Matching relational records. Is it possible using Data Minig?

Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.

Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.

Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?

Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?

Any help will be greatly appreciated.

Thanks,
Jojy

This seems like a reasonable problem for data mining. I would recommend decision trees, neural nets, or logistic regression. There is no way in SS2k5 to weight attributes, however, you can simulate gross weighting in NN and LR by duplicating columns. E.g. if you have a column "model" which you want to weight twice as much as other columns, you duplicate it to "model" and "model1" with the same data inside.

Matching records within same table

Hi all,

I've got a table named tLogonInfo with the following fields: fLogType, fDeskID, fUsername, fEventDate.

This table contains the logon and logoff history of an application. So for example the table could contain the following records:

1. 'Logon', '1', 'Jack', '2007-7-15 13:00:00'

2. 'Logon', '3', 'Paul', '2007-7-15 13:22:00'

3. 'Logoff', '1', 'Jack', '2007-7-15 14:00:00'

4. 'Logon', '2', 'Jack', '2007-7-16 10:30:00'

5. 'Logoff', '3', 'Paul', '2007-7-16 11:12:00'

6. 'Logoff', '2', 'Jack', '2007-7-16 17:10:00'

I want to make a query that has the fields Desk, User, Logon, Logoff and has the following result:

1. '1', 'Jack', '2007-7-15 13:00:00', '2007-7-15 14:00:00'

2. '3', 'Paul', '2007-7-15 13:22:00', '2007-7-16 11:12:00'

3. '2', 'Jack', '2007-7-16 10:30:00', '2007-7-16 17:10:00'

How can I do this?

if you use sql server 2005,

Code Snippet

Create Table #logindata (

[Operation] Varchar(100) ,

[Mac] Varchar(100) ,

[User] Varchar(100) ,

[OperDateTime] Varchar(100)

);

Insert Into #logindata Values('Logon','1','Jack','2007-7-15 13:00:00');

Insert Into #logindata Values('Logon','3','Paul','2007-7-15 13:22:00');

Insert Into #logindata Values('Logoff','1','Jack','2007-7-15 14:00:00');

Insert Into #logindata Values('Logon','2','Jack','2007-7-16 10:30:00');

Insert Into #logindata Values('Logoff','3','Paul','2007-7-16 11:12:00');

Insert Into #logindata Values('Logoff','2','Jack','2007-7-16 17:10:00');

;With Ordered as

(

Select

*

, Row_Number() Over(Partition By [User],[Mac] Order By [User],[Mac], [OperDateTime]) OpId

From

#logindata

)

Select

Logon.[User], LogOn.Mac, Logon.OperDateTime [Logon Time], Logoff.OperDateTime [Logoff Time]

from Ordered Logon

Left Outer Join Ordered Logoff On

Logon.[User] = Logoff.[User]

and Logon.[Mac] = Logoff.[Mac]

and Logon.OpId = Logoff.OpId -1

Where

Logon.Operation='Logon'

and Logoff.Operation='Logoff'

Drop table #logindata

|||

For SQL Server 2000,

Code Snippet

Create Table #logindata (

[Operation] Varchar(100) ,

[Mac] Varchar(100) ,

[User] Varchar(100) ,

[OperDateTime] Varchar(100)

);

Insert Into #logindata Values('Logon','1','Jack','2007-7-15 13:00:00');

Insert Into #logindata Values('Logon','3','Paul','2007-7-15 13:22:00');

Insert Into #logindata Values('Logoff','1','Jack','2007-7-15 14:00:00');

Insert Into #logindata Values('Logon','2','Jack','2007-7-16 10:30:00');

Insert Into #logindata Values('Logoff','3','Paul','2007-7-16 11:12:00');

Insert Into #logindata Values('Logoff','2','Jack','2007-7-16 17:10:00');

Select

[user],

[mac],

[OperDateTime] as [Logon Time],

(Select Min([OperDateTime])

From #logindata Sub Where

[Operation]='Logoff'

And Sub.[user]=Main.[user]

And Sub.[mac]=Main.[mac]) as [Logoff Time]

from #logindata Main

Where

[Operation]='Logon'

Drop table #logindata

Matching records using Business Intelligence Studio

Hello,

I have a database with thousands of records that contain personal details of customers. Some of these records pertain to the same customer - however, they have been submitted by different people, so they differ slightly in detail.

I've been looking to see if any of the data mining tools provided by Business Intelligence Studio in SQL Server 2005 will enable me to achieve a high degree of accuracy in matching records that pertain to the same customer. From what I can see, these tools seem more suited to making general predictions based on large groupings rather than the kind of precise prediction I am looking for.

So I'd appreciate it if anyone could tell me if there is any way I could use Business Intelligence Studio to match these 'duplicate' records together, or whether I will have to create a more SQL-based solution which attempts to match the customer records using SELECT statements and making assumptions about the data.

TIA,

Kweri

One solution is to start by creating an Integration Services project.

In the project, define a Data Flow task and add the following transforms:

- a Data Source transform, which reads from your database

- a Fuzzy Matching transform

- a Data Destination transform

The Fuzzy Matching Integration Services transform is intended to resolve the kind of problem you describe (matching records based on similarity)