--https://stackoverflow.com/questions/77114607/how-to-fill-in-records-where-dates-have-overlapped
CREATE TABLE Patient_Locations (
Patient nvarchar(100) NOT NULL
, Admission_Date date NOT NULL
, Discharge_Date date NULL
, Location nvarchar(100)
)
INSERT INTO Patient_Locations (Patient, Admission_Date, Discharge_Date, Location)
VALUES ('ABC', '20210102', NULL, 'NURSING HOME')
, ('ABC', '20210203', '20210204', 'ICU')
, ('ABC', '20210410', '20210413', 'HOSPITAL')
;WITH Next_Known_Dates (Patient, Admission_Date, Discharge_Date, Location, NEXT_Admission_Date)
AS (
SELECT Patient
, Admission_Date
, Discharge_Date
, Location
, NEXT_Admission_Date = LEAD(Admission_Date, 1) OVER (Partition by Patient ORDER BY Admission_Date)
FROM Patient_Locations
)
-- First row where Discharge_Date is Missing. Replace by next known period
SELECT Patient
, Admission_Date
, Discharge_Date = NEXT_Admission_Date
, Location
--, NEXT_Admission_Date
FROM Next_Known_Dates
WHERE Discharge_Date IS NULL
UNION ALL
-- Just normal rows
SELECT Patient
, Admission_Date
, Discharge_Date
, Location
FROM Patient_Locations
WHERE Discharge_Date IS NOT NULL
UNION ALL
--Little magic. Replace missing dates with 'normal' Discharge_date and NEXT_Admission_Date
SELECT Patient_Locations.Patient
, Next_Known_Dates.Discharge_Date
, Next_Known_Dates.NEXT_Admission_Date
, Patient_Locations.Location
FROM Patient_Locations
JOIN Next_Known_Dates
ON Next_Known_Dates.Patient = Patient_Locations.Patient
AND Next_Known_Dates.Admission_Date > Patient_Locations.Admission_Date
WHERE Patient_Locations.Discharge_Date IS NULL
AND Next_Known_Dates.Discharge_Date IS NOT NULL
ORDER BY 1, 2, 3
Telegram: DB developers channel