Retrieve records =24 hours for given Location and Customer id

Question

Hi All,

I want to retrieve records which are >=24 hours for given location and customer id. How do I achieve this without having many Multi-row tools?

Original Data set

LOCATION_IDCUSTOMER_IDDATESEQUENCE_NO20010020414707902018-08-24 02:10:00120010020414707902018-08-25 21:01:00220010020414707902018-08-30 11:20:00320010020414707902018-09-01 02:16:00420010020414707902018-09-02 16:32:00520010020414707902018-09-05 23:45:00620010020414707902018-09-06 11:51:00720010020414707902018-09-07 02:06:00820010020414707902018-09-07 16:15:00920010020414707902018-09-08 04:33:001020010020414707902018-09-08 16:56:001120010020414707902018-09-09 07:24:001220010020414707902018-09-10 09:00:001320010020414707902018-09-13 10:02:001420010020414707902018-09-14 02:24:001520010020415869602018-08-16 22:15:00120010020415869602018-08-17 11:48:00220010020415869602018-08-18 02:56:00320010020415869602018-08-23 11:40:004

Need to get

LOCATION_IDCUSTOMER_IDDATESEQUENCE_NO20010020414707902018-08-24 02:10:00120010020414707902018-08-25 21:01:00220010020414707902018-08-30 11:20:00320010020414707902018-09-01 02:16:00420010020414707902018-09-02 16:32:00520010020414707902018-09-05 23:45:00620010020414707902018-09-07 02:06:00820010020414707902018-09-08 04:33:001020010020414707902018-09-09 07:24:001220010020414707902018-09-10 09:00:001320010020414707902018-09-13 10:02:001420010020415869602018-08-16 22:15:00120010020415869602018-08-18 02:56:00320010020415869602018-08-23 11:40:004

Thanks,

Dhanushka

danilang · Accepted Answer

Hi @Dhanushka_A

you can do it in a single Multi-Row tool if you use the following formula, grouped by Location_id and customer_id

if isnull([row-1:timediff]) then
	0
elseif [Row-1:TimeDiff]+datetimediff([DATE],[Row-1:DATE],"seconds")>=24*60*60 then
	0
else
	[Row-1:TimeDiff]+ datetimediff([DATE],[Row-1:DATE],"seconds")
endif

The first if handles the first record in every group.  The next one checks if the previous time difference + the current time difference >= 24 hours and sets the time difference for the current row to 0.  The else clause adds the previous difference to the current and stores that.  If effectively creates a running total that resets to 0 if it goes above 24 hours.  Then you discard all the records that have a non-zero difference

Dan