Find sequential discharge date from multiple discharge date

Create one table variable and insert the data as need :


DECLARE @table table(patID INT,AdmDate Datetime,DisDate Datetime)

INSERT INTO @table
values(1,'2012-01-10','2012-01-15'),
(1,'2012-01-16','2012-01-20'),
(1,'2012-01-21','2012-01-25'),
(2,'2012-01-10','2012-01-15'),
(2,'2012-01-17','2012-01-20')

After inserting data here is the query to find the sequential discharge date.


;WITH cte (PatientId, AdmDt, Disdt, AdmDt2, Rnk) AS
(
SELECT t1.PatientID, t1.AdmissionDate, t1.DischargeDate, t2.AdmissionDate, ROW_NUMBER() OVER(PARTITION BY t1.PatientId ORDER BY t1.PatientId, t1.AdmissionDate, t1.DischargeDate) AS Rnk
FROM @table t1
LEFT JOIN @table t2 ON (t1.PatientID = t2.PatientID AND DATEADD(DAY, 1, t1.DischargeDate) = t2.AdmissionDate)
),
cte1 (PatientId, AdmDt, DisDt, NextRnk) AS
(
SELECT D1.PatientId, D1.AdmDt, D1.Disdt,
CASE WHEN D1.AdmDt2 IS NULL THEN D1.Rnk ELSE  MIN(D2.Rnk) End AS NextRnk
FROM cte D1
LEFT JOIN cte D2 ON (D1.PatientId = D2.PatientId AND D2.Rnk > D1.Rnk AND D2.AdmDt2 IS NULL)
GROUP BY D1.PatientId, D1.AdmDt, D1.Disdt,D1.AdmDt2, D1.Rnk
)
SELECT D1.PatientId, MIN(D1.AdmDt) AS AdminssionDate, MAX(D1.DisDt) AS DischargeDate
FROM cte1 D1
GROUP BY D1.PatientId, D1.NextRnk
ORDER BY D1.PatientId, D1.NextRnk

This will gives the output as :