Created TransferReady query

Created_TransferReady query provides all possible Transfer Ready dates from Transfer Ready DtTm field or Transfer Ready DtTm tmp entry.

It is used by Sub populate_created_transferDelay() to generate transfer delays in Created transferDelay table.

The results are used for Transfer Delay (Critical Care) and Transfer Delay (Medicine).

Log

• 2024-11-22 - updated to impute reasonable TR dtms for the period 2009-10 where we did not collect Transfer Ready DtTm field

Still need to import this into master CFE, the version I have right now is messed up. Here it is:

• SMW

• Cargo

• Categories

SELECT *
FROM (SELECT tmp_TransferReady.D_ID, tmp_TransferReady.[dttm] AS DtTm, tmp_TransferReady.combiner
FROM (SELECT L_TmpV2.D_ID, [date_var]+[time_var] AS dttm, L_TmpV2.int_var AS combiner FROM L_TmpV2 WHERE (((L_TmpV2.Project)="Transfer Ready DtTm")))  AS tmp_TransferReady 

union 

select D_ID, 
iif(arrive_dttm>#2010-05-01# and Arrive_dttm <= #2011-12-01#, 

iif(
arrive_dttm + 
(SELECT Avg([Transfer_Ready_DtTm]-[arrive_dttm]) AS Imp_trans_delay
FROM L_Log
WHERE (((L_Log.Arrive_DtTm)>#11/1/2009# And (L_Log.Arrive_DtTm)<#5/1/2010#))) 
> L_Log.dispo_dttm, 
L_Log.dispo_dttm,
arrive_dttm + 
(SELECT Avg([Transfer_Ready_DtTm]-[arrive_dttm]) AS Imp_trans_delay
FROM L_Log
WHERE (((L_Log.Arrive_DtTm)>#11/1/2009# And (L_Log.Arrive_DtTm)<#5/1/2010#))) 
)

, transfer_ready_dttm)
 as dttm, null as combiner
from l_Log

)  AS old_and_new_TR
WHERE (((old_and_new_TR.dttm) Is Not Null))
ORDER BY old_and_new_TR.dttm;

• 2022-10-05 - updated query to have no null/blank entries
• 2022-09-21 - changed the query to use ALL available transfer ready dttms from old and new, in a union query. This was decided because Julie pointed out that the two methods were used inconsistently during the PatientFollow Project transition, and because the way we use this to calculate delays will work even if there are additional dates, it just might over-estimate the wasted days.
• 2022-06-28 - updated to use query created_PatientFollow to pull old Transfer Ready DtTm field until change to Transfer Ready DtTm tmp entry