Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I get the latest record in each month for each customer? Expand / Collapse
Author
Message
Posted Wednesday, February 11, 2009 1:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 8, 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
Posted Wednesday, February 11, 2009 2:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 4:23 AM
Points: 879, Visits: 282
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



Regards,
Nitin

Post #654554
Posted Friday, November 27, 2009 9:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse