Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day,
I have the following two tables, and have multiple dates to create a master calendar.
Metrics:
the number of times a patient was admitted to a department.
the the of days between startevent and endevent time based on the master calendar.
since the fields are very similar, I create a link table with a canonical date
with admission date
and dates created using iterno() function to find dates between start date and end date and used the date field
in master calendar off of this date.
I also concatenated the tables.
My question, when I use a set statement to get the difference between the date fields it is giving me a false count.
Table1:
LOAD
Patient,
Unit,
timestamp#(Arrive, 'MM/DD/YYYY hh:mm') as Arrive,
timestamp#(Discharge, 'MM/DD/YYYY hh:mm') as Discharge
INLINE [
Patient, Unit, Arrive, Discharge
P1, A, 01/01/2010 09:00, 01/01/2010 12:30
P2, A, 01/01/2010 09:30, 01/01/2010 10:30
P3, A, 01/01/2010 11:15, 01/01/2010 16:40
P4, A, 01/01/2010 18:30, 01/02/2010 02:10
P5, A, 01/01/2010 01:15, 01/01/2010 01:30
P6, B, 01/01/2010 10:00, 01/01/2010 11:30
P7, B, 01/01/2010 10:15, 01/01/2010 10:30
P8, B, 01/01/2010 10:30, 01/01/2010 15:15
P9, B, 01/01/2010 13:00, 01/01/2010 15:30
P10,B, 01/02/2010 01:15, 01/02/2010 05:30
]
;
Table2:
LOAD
Patient,
Unit,
timestamp#(Arrive, 'MM/DD/YYYY hh:mm') as Arrive,
timestamp#(Discharge, 'MM/DD/YYYY hh:mm') as Discharge
INLINE [
Patient, Unit, Arrive, Discharge, EventStart, EventEnd
P1, A, 01/01/2010 09:00, 01/04/2010 12:30, 01/02/2010 09:30, 01/03/2010 12:30
P2, A, 01/01/2010 09:30, 01/04/2010 10:30, 01/02/2010 09:10, 01/03/2010 12:30
P3, A, 01/01/2010 11:15, 01/04/2010 16:40, 01/01/2010 09:00, 01/01/2010 12:30
P4, A, 01/01/2010 18:30, 01/04/2010 02:10, 01/01/2010 18:30, 01/02/2010 02:10
P5, A, 01/01/2010 01:15, 01/04/2010 01:30, 01/01/2010 01:15, 01/04/2010 01:30
P6, B, 01/01/2010 10:00, 01/04/2010 11:30, 01/01/2010 10:00, 01/01/2010 11:30
P7, B, 01/01/2010 10:15, 01/04/2010 10:30, 01/01/2010 10:15, 01/01/2010 10:30
P8, B, 01/01/2010 10:30, 01/04/2010 15:15, 01/01/2010 10:30, 01/01/2010 15:15
P9, B, 01/01/2010 13:00, 01/04/2010 15:30, 01/01/2010 13:00, 01/01/2010 15:30
P10,B, 01/02/2010 01:15, 01/04/2010 05:30, 01/02/2010 01:15, 01/02/2010 05:30
]
;
I would like to credit