February 11, 2009 at 1:36 am
How do I get the latest record in each month for each customer? Eg:
TableA
IDValuePerformedDtm
1111015/02/2008
1211515/02/2008
1212025/03/2008
1312009/12/2008
1411609/12/2008
1311825/12/2008
1111727/12/2008
1211629/12/2008
expected result:
IDValuePerformedDtm
1111727/12/2008
1211629/12/2008
1311825/12/2008
1411609/12/2008
February 11, 2009 at 2:28 am
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
November 27, 2009 at 9:39 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply