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 ««12

Finding the MIN and MAX date - How do I approach this? Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 9:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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()) EndDate
FROM CTE c
JOIN SampleData s ON c.EMPID = s.EMPID
AND c.PrimaryRater = s.PrimaryRater
AND c.EndDate = s.EffectiveDate
LEFT
JOIN 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 Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521730
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse