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')

/* 
    for each patient encounter, iterate over every date that falls between the day it started and day it ended;
        record resultant dates in a temp table
*/
drop table if exists #encounterDates
create table #encounterDates (encounterId int, encounterDate date)

declare encounter cursor
for 
    select id, AdmitDTS, DischargeDTS 
    from encounters;

open encounter
declare 
    @id int,
    @AdmitDTS datetime,
    @DischargeDTS datetime
fetch next from encounter into @id, @AdmitDTS, @DischargeDTS
while @@FETCH_STATUS = 0
begin

declare 
        @i int = 0,
        @n int = (select datediff(dd, @AdmitDTS, @DischargeDTS))
    while @i <= @n
    begin
        insert into #encounterDates (encounterId, encounterDate)
        select @id, dateadd(dd, @i, @AdmitDTS)
        set @i += 1
    end

fetch next from encounter into @id, @AdmitDTS, @DischargeDTS
end

close encounter;
deallocate encounter;

/*
    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 
        ed.encounterDate, s.id as shiftId, s.StartTS, s.EndTS,
        cast(ed.encounterDate as datetime) + cast(s.StartTS as datetime) as ShiftStartDTS,
        cast(ed.encounterDate as datetime) + cast(s.EndTS as datetime) as ShiftEndDTS
    from #encounterDates ed
        left join encounters e on ed.encounterId = e.id
        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)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(86 rows affected)

(9 rows affected)