Sequencing Date Ranges


The original question:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/980b137f-cf5d-4a91-8273-2b61aba79b0e/sequencing-date-ranges?forum=transactsql#fb3aafdc-a15d-4322-b443-bc2e3707ba18

Here is my answer:

 CREATE TABLE #DateRanges 
( 
PatientID integer NULL, 
StartDate date NULL, 
EndDate date NULL 
);

INSERT INTO #DateRanges (PatientID, StartDate, EndDate)
VALUES
( 1, '08-29-2012', '10-03-2012'),
( 1, '10-31-2012', '11-20-2012'),
( 1, '07-04-2013', '07-17-2013'),
( 1, '07-05-2013', '07-05-2013'),
( 2, '12-15-2008', '01-14-2009'),
( 2, '01-15-2009', '01-21-2009'),
( 2, '01-22-2009', '02-10-2009'),
( 2, '04-04-2011', '04-08-2011'),
( 2, '02-02-2012', '02-15-2012'),
( 2, '12-19-2012', '12-28-2012'),
( 2, '05-09-2013', '05-09-2013'),
( 2, '05-13-2013', '05-20-2013'),
( 3, '09-26-2001', '10-16-2001'),
( 3, '07-01-2002', '07-29-2002'),
( 3, '06-30-2003', '07-25-2003'),
( 3, '09-15-2003', '09-15-2003'),
( 3, '12-10-2003', '12-10-2003'),
( 4, '07-27-2000', '07-27-2000'),
( 4, '11-07-2000', '11-16-2000'),
( 4, '11-07-2000', '11-27-2000'),
( 4, '04-19-2001', '05-01-2001'),
( 4, '04-20-2001', '04-20-2001'),
( 4, '09-13-2001', '09-13-2001'),
( 4, '09-19-2001', '09-19-2001'),
( 4, '10-05-2001', '10-05-2001'),
( 4, '09-13-2002', '09-24-2002')

;with mycte as (SELECT

PatientID, StartDate, EndDate, TreatmentSequenceNumber = NULL
,row_number() Over(Partition by PatientID Order By StartDate) rn

FROM #DateRanges)
,mycte1 as (
SELECT m1.PatientID, m1.StartDate, m1.EndDate,m1.rn, CASE WHEN Datediff(day,m2.EndDate,m1.StartDate)<=5 THEN 0 Else 1 End cnt  
from mycte m1 LEFT JOIN mycte m2 On m1.PatientID=m2.PatientID AND m1.rn=m2.rn+1
)

Select m1.PatientID, m1.StartDate, m1.EndDate, sum(m2.cnt) TreatmentSequenceNumber
 from mycte1 m1 inner join mycte1 m2 on m1.PatientID=m2.PatientID and m1.rn>=m2.rn

GROUP BY m1.PatientID, m1.StartDate, m1.EndDate
ORDER BY m1.PatientID,m1.StartDate


DROP TABLE #DateRanges
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s