Selecting Distinct / Max / Most Recent Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting Distinct / Max / Most Recent Records

Author

Topic

scullee
Posting Yak Master

103 Posts

Posted - 2003-03-25 : 19:23:26

Well it seems im asking a question a day to this place now.

I am currently writing a report that needs to show the number of customers that were active at the end of each month for each subscription and channel in our system (channel = who it is sold through and subscription is what they sell). The data for this is going to be retrieved daily but i want to only get 1 record for each criteria.

eg

Each day there is an extration that calculates the number of active customer for each subscription type. For one month we would have lots of records. Eg

Date Collected Subscription Channel Number
01/Jan/2003 Basic Melbourne 200
01/Jan/2003 Luxury Melbourne 50
01/Jan/2003 Basic Sydney 300
02/Jan/2003 Basic Melbourne 205
02/Jan/2003 Luxury Melbourne 49
02/Jan/2003 Basic Sydney 320
02/Jan/2003 Luxury Sydney 1
.....
31/Jan/2003 Basic Melbourne 280
31/Jan/2003 Luxury Melbourne 75
31/Jan/2003 Basic Sydney 260
01/Feb/2003 Basic Melbourne 281
01/Feb/2003 Luxury Melbourne 65
01/Feb/2003 Basic Sydney 280
02/Feb/2003 Basic Melbourne 265
02/Feb/2003 Luxury Melbourne 70
02/Feb/2003 Basic Sydney 290
02/Feb/2003 Luxury Sydney 1
.....
28/Feb/2003 Basic Melbourne 280
28/Feb/2003 Luxury Melbourne 75
28/Feb/2003 Basic Sydney 260

I need to write a query that will only return the last record for each month. For each subscription and channel

eg Just these records.

31/Jan/2003 Basic Melbourne 280
31/Jan/2003 Luxury Melbourne 75
31/Jan/2003 Basic Sydney 260
28/Feb/2003 Basic Melbourne 280
28/Feb/2003 Luxury Melbourne 75
28/Feb/2003 Basic Sydney 260

Because the list of channels and subscriptions change is there any way of writing this dynamically?

The only thing i could think of is to query the dates first to get a max date with the group by, then use this to select the records. Any other ideas?

Edited by - scullee on 03/25/2003 20:42:12

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-03-25 : 20:36:50

Sculle,

That is a very confusing question..

Based on your given result set..


SELECT * 
FROM Table S
WHERE EXISTS
(SELECT 1
FROM Table 
GROUP BY DATENAME(yyyy,Dates) + DATENAME(mm,Dates)
HAVING MAX(Dates) = S.DATES)

31st Feb????? What calendar is that!!!

DavidM

"SQL-3 is an abomination.."

scullee
Posting Yak Master

103 Posts

Posted - 2003-03-25 : 20:45:28

hehehe, yeah well i copied and pasted the data and forgot to update the dates back to 28 :)

Sorry if its confusing but its not a simple thing to explain.

From the way i read the sp it will limit the query "SELECT *
FROM Table S" to only return the records when they are for the last day in the month.

If thats what it does then its perfect.

THanks :)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-03-25 : 21:00:07

Scullee,

It is not the last day of the month.. it is the last entered day of the month... If you need the last day then you will need a helper function or calendar table

DavidM

"SQL-3 is an abomination.."

scullee
Posting Yak Master

103 Posts

Posted - 2003-03-25 : 21:29:44

Its perfect then. I was looking for the last day entered for the month anyway.

I would buy you a beer to thank you for the help but i got no idea who you are or where you live :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources