Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Finding the MIN and MAX date - How do I approach this? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, December 10, 2013 9:16 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 6:47 PM Points: 2,788, Visits: 5,972
 Would this help?`WITH SampleData([EMPID],[EffectiveDate],[PrimaryRater], [PrimaryReviewer])AS( --This is sample data SELECT '12345',convert(datetime,'10/10/2001'),'A12345', 'A67890' UNION ALL SELECT '12345','07/12/2013','A12345', 'B67890' UNION ALL SELECT '12345','08/18/2002','A12345', 'C67890' UNION ALL SELECT '12345','07/17/1966','A12345', 'D67890' UNION ALL SELECT '12345','01/01/1966','B12345', 'E67890' )--Solution starts here,CTE AS( SELECT EMPID, PrimaryRater, MIN( EffectiveDate) StartDate, MAX( EffectiveDate) EndDate, DENSE_RANK() OVER( PARTITION BY EMPID ORDER BY Min(EffectiveDate)) RN FROM SampleData GROUP BY EMPID, PrimaryRater)SELECT c.EMPID, c.StartDate, c.PrimaryRater, s.PrimaryReviewer, ISNULL( c2.StartDate, GETDATE()) EndDateFROM CTE cJOIN SampleData s ON c.EMPID = s.EMPID AND c.PrimaryRater = s.PrimaryRater AND c.EndDate = s.EffectiveDateLEFTJOIN CTE c2 ON c.EMPID = c2.EMPID AND c.RN = c2.RN - 1` Luis C.I am a great believer in luck, and I find the harder I work the more I have of it. Stephen LeacockForum Etiquette: How to post data/code on a forum to get the best help
Post #1521730

 Permissions

 Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.