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')
    
/*
    iterate over each day in the range of possible dates that a patient was present, recording all days in a temp table
*/
drop table if exists #AllDays
create table #AllDays (encounterDate date)
declare 
    @FirstAdmission date = (select min(AdmitDTS) from encounters),
    @LastDischarge date = (select max(DischargeDTS) from encounters)
declare @DateCounter date = @FirstAdmission
while @DateCounter <= @LastDischarge
begin
    insert into #AllDays (encounterDate)
    select @DateCounter

set @DateCounter = dateadd(dd, 1, @DateCounter)
end

/*
    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