List of dates between two dates

[thomas_evans]

Hi All,

Can someone help me to make a list of dates between two dates please.

Example data:

Cert:

Load * Inline [

StartDate, ExpiryDate, PersonID, CertID

'2010-01-15', '2010-01-17', 999, 3,

'2010-01-16', '2010-01-20', 888, 2,

'2011-05-17', '2010-05-22', 555, 1

];

Expected result:

PersonID, CertID, Date

999, 3, 2010-01-15

999, 3, 2015-01-06

999, 3, 2016-01-17

888, 2, 2010-01-16

888, 2, 2010-01-17

888, 2, 2010-01-18

888, 2, 2010-01-19

888, 2, 2010-01-20

555, 1, 2011-05-17

555, 1, 2011-05-18

555, 1, 2011-05-19

555, 1, 2011-05-20

555, 1, 2011-05-21

555, 1, 2011-05-22

FTI multiple certID may exist across same timeframe.

Thanks,

Thomas

[OmarBenSalem]

table:

LOAD CertID,PersonID,StartDate,ExpiryDate,Date(StartDate + IterNo() - 1) as Date

While StartDate + IterNo() - 1 <= ExpiryDate

;

Load date(Date#(StartDate,'YYYY-MM-DD')) as StartDate,date(Date#(ExpiryDate,'YYYY-MM-DD')) as ExpiryDate, PersonID, CertID  Inline [

StartDate, ExpiryDate, PersonID, CertID

'2010-01-15', '2010-01-17', 999, 3

'2010-01-16', '2010-01-20', 888, 2

'2010-05-17', '2010-05-22', 555, 1

];

result:

[sunny_talwar · Accepted solution]

Try this

Cert:

LOAD CertID,

PersonID,

Date(StartDate + IterNo() - 1) as Date

While StartDate + IterNo() - 1 <= ExpiryDate;

LOAD * INLINE [

    StartDate, ExpiryDate, PersonID, CertID

    2010-01-15, 2010-01-17, 999, 3

    2010-01-16, 2010-01-20, 888, 2

    2011-05-17, 2011-05-22, 555, 1

];

[OmarBenSalem]

I, for once, was quicker than u on this

[sunny_talwar]

Omar, my friend... you are the man!!

[pallavi_92]

I have a similar scenario, but my data is huge.

It's taking ages to get this new table with all the dates. Is there any solution for that ?