FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

/*
    create table for sample data
*/
create table encounters (
    id int,
    DRGWeight decimal(10,2),
    AdmitDTS datetime,
    DischargeDTS datetime,
)
insert into encounters 
values 
    (1, '1.81', '2024-01-18 10:17', '2024-01-19 18:15'),
    (2, '1.78', '2024-02-07 11:37', '2024-02-12 13:49'),
    (3, '0.40', '2024-01-09 05:41', '2024-01-10 15:10'),
    (4, '1.95', '2024-01-21 08:10', '2024-01-25 10:00')

/*
    create table of shifts
*/
create table shifts (
    id int unique not null,
    StartTS time unique not null,
    EndTS as cast(dateadd(s, -1, dateadd(hh, 4, cast(StartTS as datetime2))) as time)
)
insert into shifts (id, StartTS)
values 
    (1, '00:00:00'),
    (2, '04:00:00'),
    (3, '08:00:00'),
    (4, '12:00:00'),
    (5, '16:00:00'),
    (6, '20:00:00')
    
drop table if exists #AllDays
declare 
    @FirstAdmission date = (select min(cast(AdmitDTS as date)) from encounters),
    @LastDischarge date = (select max(cast(DischargeDTS as date)) from encounters)
;with AllDays as (
    select 
        @FirstAdmission as [Date]
    from encounters e

union all

select
        dateadd(dd, 1, ad.[Date]) as [Date]
    from AllDays ad
    where dateadd(dd, 1, ad.[Date]) <= @LastDischarge
)
select distinct [Date] as encounterDate 
into #AllDays
from AllDays
order by [Date]
option (maxrecursion 0)

/*
    get all possible days that at least one patients was present, and shifts that started while they were present
*/
drop table if exists #shiftDays
select 
    row_number() over(order by x.encounterDate, x.StartTS) as shiftDayId,
    x.*
into #shiftDays
from 
(
    select 
        ad.encounterDate, s.id as shiftId, s.StartTS, s.EndTS,
        cast(ad.encounterDate as datetime) + cast(s.StartTS as datetime) as ShiftStartDTS,
        cast(ad.encounterDate as datetime) + cast(s.EndTS as datetime) as ShiftEndDTS
    from encounters e
        left join #AllDays ad on ad.encounterDate between cast(e.AdmitDTS as date) and cast(e.DischargeDTS as date)
        cross join shifts s
) x
where 
    (
        /* not later than the last shift of the final encounter */
        cast(x.encounterDate as datetime) + cast(x.EndTS as datetime) <= (select max(e.DischargeDTS) from encounters e)
    )
    and
    (
        /* not earlier than the first shift of the first encounter */
        cast(x.encounterDate as datetime) + cast(x.EndTS as datetime) >= (select min(e.AdmitDTS) from encounters e)
    )
    
/*
    get census time, as the end of the current shift, for all shifts during which a given patient was present
*/
select
    e.id,
    e.DRGWeight,
    e.AdmitDTS,
    e.DischargeDTS, 
    sd.shiftEndDTS as CensusDTS,
    sd.shiftId as Shift
from encounters e
    left join #shiftDays sd on 
    (
        e.AdmitDTS < sd.ShiftEndDTS            /* patient admitted before shift ends */
        and e.DischargeDTS > sd.ShiftStartDTS    /* patient discharged after shift begins */
    )
where e.id = 1
order by e.id, CensusDTS

id	DRGWeight	AdmitDTS	DischargeDTS	CensusDTS	Shift
1	1.81	2024-01-18 10:17:00.000	2024-01-19 18:15:00.000	2024-01-18 11:59:59.000	3
1	1.81	2024-01-18 10:17:00.000	2024-01-19 18:15:00.000	2024-01-18 15:59:59.000	4
1	1.81	2024-01-18 10:17:00.000	2024-01-19 18:15:00.000	2024-01-18 19:59:59.000	5
1	1.81	2024-01-18 10:17:00.000	2024-01-19 18:15:00.000	2024-01-18 23:59:59.000	6
1	1.81	2024-01-18 10:17:00.000	2024-01-19 18:15:00.000	2024-01-19 03:59:59.000	1
1	1.81	2024-01-18 10:17:00.000	2024-01-19 18:15:00.000	2024-01-19 07:59:59.000	2
1	1.81	2024-01-18 10:17:00.000	2024-01-19 18:15:00.000	2024-01-19 11:59:59.000	3
1	1.81	2024-01-18 10:17:00.000	2024-01-19 18:15:00.000	2024-01-19 15:59:59.000	4
1	1.81	2024-01-18 10:17:00.000	2024-01-19 18:15:00.000	2024-01-19 19:59:59.000	5

(4 rows affected)

(6 rows affected)

(35 rows affected)

(86 rows affected)

(9 rows affected)