4 Replies Latest reply: Jun 25, 2018 8:43 AM by Sunny Talwar

# List of dates between two dates

Hi All,

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

Example data:

Cert:

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

• ###### Re: List of dates between two dates

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:

• ###### Re: List of dates between two dates

Try this

Cert:

PersonID,

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

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

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

];

• ###### Re: List of dates between two dates

I, for once, was quicker than u on this

• ###### Re: List of dates between two dates

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