Create dates between 2 dates

[kev6brown]

Hi,

I have a table in excel that I need to bring into Sense.  What I need is the table to show all dates between the admissiondate and the discharge date so from - 

PatientID	EventNumber	AdmissionDate	DischargeDate	SequenceID
156858	1	13/09/2017 10:00	25/10/2017 13:30	4
169650	1	10/08/2017 12:00	11/10/2017 14:00	6
169770	1	18/08/2017 12:00	17/10/2017 14:34	7
169329	1	05/09/2017 12:00	31/10/2017 09:54	8
3000177	1	27/09/2017 12:00	12/01/2018 12:00	9

to

PatientID	EventNumber	AdmissionDate	DischargeDate	SequenceID	Date
156858	1	13/09/2017 10:00	25/10/2017 13:30	4	13/09/17
156858	1	13/09/2017 10:00	25/10/2017 13:30	4	14/09/17
156858	1	13/09/2017 10:00	25/10/2017 13:30	4	15/09/17
156858	1	13/09/2017 10:00	25/10/2017 13:30	4	16/09/17
156858	1	13/09/2017 10:00	25/10/2017 13:30	4	17/09/17

etc

Kev

[PrashantSangle · Accepted solution]

Try
Load
PatientID,
EventNumber,
AdmissionDate,
DischargeDate,
SequenceID,
Date(AdmissionDate + IterNo() -1) as Date
from tableName
while AdmissionDate + IterNo() - 1 < = DischargeDate;

Regards,

[kev6brown]

This creates millions of rows

[PrashantSangle]

It is as per your requirement only.

[kev6brown]

its because i had null, thank you

[kev6brown]

How can i set the discharge date to today and it still work? I've got the below but i think something with the "While" needs doing

vent:
LOAD
PatientID,
EventNumber,
AdmissionDate,
AdmissionDate as CalendarKey,
Date(AdmissionDate + IterNo() -1) as Date,
if(DischargeDate='NULL',Today(),DischargeDate) as DischargeDate,
SequenceID
FROM [..\..\.xlsx]
(ooxml, embedded labels, table is ImsEvent)
while AdmissionDate + IterNo() < DischargeDate;