How do I get the latest record in each month for each customer?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » How do I get the latest record in each month...

How do I get the latest record in each month for each customer?

Rate Topic

Display Mode

Topic Options

Author

Message

siewlian908

Posted Wednesday, February 11, 2009 1:36 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 08, 2010 8:49 PM
Points: 11, Visits: 36

How do I get the latest record in each month for each customer? Eg:

TableA
ID Value PerformedDtm
111 10 15/02/2008
121 15 15/02/2008
121 20 25/03/2008
131 20 09/12/2008
141 16 09/12/2008
131 18 25/12/2008
111 17 27/12/2008
121 16 29/12/2008

expected result:
ID Value PerformedDtm
111 17 27/12/2008
121 16 29/12/2008
131 18 25/12/2008
141 16 09/12/2008

Post #654517

nitinpatel31

Posted Wednesday, February 11, 2009 2:28 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 10:39 AM
Points: 879, Visits: 261

I am not sure what exactly you want? Do you want latest record for Id or latest record for each month? From your example it seems you want latest record for each Id. If it is the case then below is your solution.

CREATE TABLE #T (Id INT, Val INT, PerformedDtm DATE)

INSERT INTO #t (Id, Val, PerformedDtm)
SELECT 111 Id ,10 Val ,CAST('02/15/2008' AS DATE) PerformedDtm
UNION ALL SELECT 121 ,15 ,'02/15/2008'
UNION ALL SELECT 121 ,20 ,'03/25/2008'
UNION ALL SELECT 131 ,20 ,'12/09/2008'
UNION ALL SELECT 141 ,16 ,'12/09/2008'
UNION ALL SELECT 131 ,18 ,'12/25/2008'
UNION ALL SELECT 111 ,17 ,'12/27/2008'
UNION ALL SELECT 121 ,16 ,'12/29/2008'

SELECT #T.* FROM #T
INNER JOIN (SELECT Id, MAX(PerformedDtm) PerformedDtm FROM #T GROUP BY Id) AS T ON T.Id = #T.Id AND T.PerformedDtm = #T.PerformedDtm
ORDER BY #T.Id

DROP TABLE #T

Best Regards
Nitin

Try this: www.EnlinkURL.com

Post #654554

robert.dickens

Posted Friday, November 27, 2009 9:39 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, January 28, 2011 12:36 PM
Points: 15, Visits: 25

why not try this

select max(id) as id , -- latest primary key column
customer , -- customer key value
max(event_date) as event_date -- latest day of the month
from mytable
group by customer , -- base criteria
datepart(year,event_date) , -- group by year
datepart(month,event_date) -- group by month

the output of this query will give a a single row for each customer for each year and month

Post #825675

« Prev Topic | Next Topic »

Permissions