Partition rows by id and DateColumn or DateColumn + 1

Hello all. I'm trying to partition out data by patient stay and select the stay with the greatest discharge date. The only thing I'm struggling with is, multiple stays where the admit date is the same or 1 day later have to be treated as the same, and again I take the one with the latest discharge date.

SQL server 2016

create table #HospitalStays

(

PatientId int

,AdmitDate date

,DischargeDate date

,DaysToFollowUp int

)

insert into #HospitalStays

Values

(123456, '2024-08-01', '2024-08-01', 14)

,(123456, '2024-08-02', '2024-08-05', 30)

,(123456, '2024-08-07', '2024-08-08', 30)

select

patientId

,AdmitDate

,DischargeDate

,DaysToFollowUp

,row_number() over (partition by patientId, AdmitDate order by DischargeDate desc, DaysToFollowUp) as rownum

from

#HospitalStays

So in the above how do I make Admit date either admitDate or dateadd(dd, 1, admitDate)

I'm doing it with a cursor, but I have a feeling my architect is not going to want a cursor in the proc.

Much appreciated!

rodgerkong

Aug 17, 2024

thorrocks

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
Iron Contributor
Aug 17, 2024
thorrocks
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
- thorrocks
  Copper Contributor
  Aug 19, 2024
  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
  - rodgerkong
    Iron Contributor
    Aug 20, 2024
    thorrocks
    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

Forum Discussion

Partition rows by id and DateColumn or DateColumn + 1

Share

Resources