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 Eights Group: General Forum Members Last Login: Today @ 8:17 PM Points: 8,218, Visits: 17,782
 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.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1521730

 Permissions