Time interval between start and end date

[Anonymous]

Hi,

I need help with a script that is already doing my head in. I have these 3 fields associated to an ID in a table.

[Receive Date]

[Follow up Date]

[Closed Date]

All the ID,s have Receive date and closed date but only few Id's have follow up date. I want to pull only those Id's which doesn't have a followup date but are closed less than 48-72hrs time period.

For Example:

ID1 has received date as 01/01/2018 and closed date as 01/03/2018

ID2 has received date as 01/01/2018 and closed date as 01/07/2018 and follow up date as 01/03/2018.

So the result should be only ID1 because it doesn't have a followup date and has been closed within 48hrs-72hrs.

Thanks.

[vishsaggi · Accepted solution]

Not sure if this is a good way but you can try like this. You can change the dates in the Inline data set for testing.

DateTimeTest:
LOAD *, IF(Len(Trim(FollowUpDate)) = 0 AND (IntDiff >= 48 AND IntDiff <= 72), ID) AS QualifiedIDs;
LOAD *, Num(Num#(Interval(ClosedDate - ReceivedDate, 'hh'),'##')) AS IntDiff INLINE [
ID, ReceivedDate, FollowUpDate, ClosedDate
ID1, 01/01/2018, , 01/03/2018
ID2, 01/01/2018, 01/03/2018, 01/07/2018
ID3, 01/01/2018, 01/03/2018, 01/03/2018
ID4, 01/02/2018, , 01/02/2018
ID4, 01/02/2018, , 01/05/2018
];

Your QualifiedIds Field will have the valid fields that matches your criteria.

[Anonymous]

Thank You Vishwarath,

Will try this and let you know