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
)
GO
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!
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 2016create table #HospitalStays(PatientId int,AdmitDate date,DischargeDate date,DaysToFollowUp int)GOinsert into #HospitalStaysValues (123456, ‘2024-08-01’, ‘2024-08-01’, 14) ,(123456, ‘2024-08-02’, ‘2024-08-05’, 30) ,(123456, ‘2024-08-07’, ‘2024-08-08’, 30)selectpatientId,AdmitDate,DischargeDate,DaysToFollowUp,row_number() over (partition by patientId, AdmitDate order by DischargeDate desc, DaysToFollowUp) as rownumfrom#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! Read More
