## Finding the MIN and MAX date - How do I approach this?

 Author Message Luis Cazares SSCrazy Eights Group: General Forum Members Points: 8590 Visits: 18174 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