Retrieve records >=24 hours for given Location and Customer id

[Dhanushka_A]

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_ID	CUSTOMER_ID	DATE	SEQUENCE_NO
200100	2041470790	2018-08-24 02:10:00	1
200100	2041470790	2018-08-25 21:01:00	2
200100	2041470790	2018-08-30 11:20:00	3
200100	2041470790	2018-09-01 02:16:00	4
200100	2041470790	2018-09-02 16:32:00	5
200100	2041470790	2018-09-05 23:45:00	6
200100	2041470790	2018-09-06 11:51:00	7
200100	2041470790	2018-09-07 02:06:00	8
200100	2041470790	2018-09-07 16:15:00	9
200100	2041470790	2018-09-08 04:33:00	10
200100	2041470790	2018-09-08 16:56:00	11
200100	2041470790	2018-09-09 07:24:00	12
200100	2041470790	2018-09-10 09:00:00	13
200100	2041470790	2018-09-13 10:02:00	14
200100	2041470790	2018-09-14 02:24:00	15
200100	2041586960	2018-08-16 22:15:00	1
200100	2041586960	2018-08-17 11:48:00	2
200100	2041586960	2018-08-18 02:56:00	3
200100	2041586960	2018-08-23 11:40:00	4

Need to get

LOCATION_ID	CUSTOMER_ID	DATE	SEQUENCE_NO
200100	2041470790	2018-08-24 02:10:00	1
200100	2041470790	2018-08-25 21:01:00	2
200100	2041470790	2018-08-30 11:20:00	3
200100	2041470790	2018-09-01 02:16:00	4
200100	2041470790	2018-09-02 16:32:00	5
200100	2041470790	2018-09-05 23:45:00	6
200100	2041470790	2018-09-07 02:06:00	8
200100	2041470790	2018-09-08 04:33:00	10
200100	2041470790	2018-09-09 07:24:00	12
200100	2041470790	2018-09-10 09:00:00	13
200100	2041470790	2018-09-13 10:02:00	14
200100	2041586960	2018-08-16 22:15:00	1
200100	2041586960	2018-08-18 02:56:00	3
200100	2041586960	2018-08-23 11:40:00	4

Thanks,

Dhanushka

[danilang · Accepted solution]

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

[Dhanushka_A]

Thanks Dan. I'm embarrassed to admit that I did not realize that we can use the new field "TimeDiff" within the expression until now. 

Cheers,

Dhanushka