Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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:
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
];
I, for once, was quicker than u on this
Omar, my friend... you are the man!!
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 ?