Forum Discussion
Partition rows by id and DateColumn or DateColumn + 1
What's the rule of a sequence of dates? For example: 'AdmitDate' contains ('2024-08-01', '2024-08-02', '2024-08-03'). Should they be treated as the same? In my understanding, it must be treated as one, or it will cause confusion.
Base on this, it can be resolve by Recursive CTE, Add a AdmitId to identify the rows 'treated one', then group by PatientId and AdmitId, using aggregate functions to get data you need.
WITH HT (PatientId, AdmitDate, AdmitId, DischargeDate, DaysToFollowUp) AS ( SELECT PatientId, AdmitDate, ROW_NUMBER() OVER (ORDER BY PatientId, AdmitDate), DischargeDate, DaysToFollowUp FROM #HospitalStays WHERE DATEADD(day, 1, AdmitDate) NOT IN (SELECT AdmitDate FROM #HospitalStays) UNION ALL SELECT HT.PatientId, HS.AdmitDate, HT.AdmitId, HS.DischargeDate, HS.DaysToFollowUp FROM #HospitalStays HS INNER JOIN HT ON HS.PatientId = HT.PatientId AND DATEADD(day, 1, HS.AdmitDate) = HT.AdmitDate ) SELECT PatientID, MIN(AdmitDate) AS AdmintDate, --OR use MAX to get max AdminDate of Dates treated as one MAX(DischargeDate) AS DischargeDate, MAX(DaysToFollowUp) AS DaysToFollowUp -- or use Min FROM HT GROUP BY PatientID, AdmitId
rodgerkong
I like what you did, this works if i remove the aggregates and then use the AdmitId to partition your result set. In the event that the 2+ rows that are treated as 1 have the same discharge date i need the one with the fewest follow up days. so below i need row 2 which is eliminated due to the 08-03 row.
I'll do some more testing, but looks like ill be good with this...
WITH HT (PatientId, AdmitDate, AdmitId, DischargeDate, DaysToFollowUp)
AS
(
SELECT PatientId, AdmitDate, ROW_NUMBER() OVER (ORDER BY PatientId, AdmitDate), DischargeDate, DaysToFollowUp
FROM #HospitalStays
WHERE DATEADD(day, 1, AdmitDate) NOT IN (SELECT AdmitDate FROM #HospitalStays)
UNION ALL
SELECT HT.PatientId, HS.AdmitDate, HT.AdmitId, HS.DischargeDate, HS.DaysToFollowUp
FROM #HospitalStays HS
INNER JOIN HT
ON
HS.PatientId = HT.PatientId
AND DATEADD(day, 1, HS.AdmitDate) = HT.AdmitDate
)
SELECT
PatientID
,AdmitDate
,DischargeDate
,DaysToFollowUp
,row_number() over(partition by patientid, AdmitId order by DischargeDate desc, DaysToFollowUp) as rownum
into #tmp
FROM HT
select * from #tmp where rownum = 1
To get the results you want (as I understand it), you need to add a process after finding rows treated as one. Use the window function OVER to partition the rows by PatientID and DischargeDate, then use the aggregate function MIN to replace DaysToFollowUp value in each partitions as you need. Finally use the process I mentioned earlier to get the results. Here is code:
WITH HT (PatientId, AdmitDate, AdmitId, DischargeDate, DaysToFollowUp)
AS
(
SELECT PatientId, AdmitDate, ROW_NUMBER() OVER (ORDER BY PatientId, AdmitDate), DischargeDate, DaysToFollowUp
FROM #HospitalStays
WHERE DATEADD(day, 1, AdmitDate) NOT IN (SELECT AdmitDate FROM #HospitalStays)
UNION ALL
SELECT HT.PatientId, HS.AdmitDate, HT.AdmitId, HS.DischargeDate, HS.DaysToFollowUp
FROM #HospitalStays HS
INNER JOIN HT
ON
HS.PatientId = HT.PatientId
AND DATEADD(day, 1, HS.AdmitDate) = HT.AdmitDate
)
SELECT PatientID
, MIN(AdmitDate) AS AdmitDate
, MAX(DischargeDate) AS DischargeDate
, MAX(DaysToFollowUp) AS DaysToFollowUp --Get max or min value
FROM
(
SELECT PatientID,
AdmitDate,
AdmitId,
DischargeDate AS DischargeDate,
MIN(DaysToFollowUp) OVER (PARTITION BY PatientID, DischargeDate) AS DaysToFollowUp -- Get minimal DaysToFollowUp in partition which has same patienID and DischargeDate
FROM HT
)A
GROUP BY PatientID, AdmitId
