Add number of previous rows which are greater than current row date

[jeffyfj]

Hi

I have a table which has a row per contract id. I have two datetime stamps - created and assigned. I need to group the data by created date, sort by created datetime column and calculate a new column "Arrival volume" which gives the sum of previous rows which are greater than current row assigned datetime.

Please suggest an efficient way to handle this query.

contract id	created	assigned	created date	Arrival volume
8120821	14-09-2022 02:03	14-09-2022 05:44	14-09-2022	0
8120861	14-09-2022 02:31	14-09-2022 10:45	14-09-2022	0
8120891	14-09-2022 07:03	14-09-2022 10:30	14-09-2022	1
8120941	14-09-2022 08:32	14-09-2022 11:11	14-09-2022	0
8121021	14-09-2022 08:40	14-09-2022 10:57	14-09-2022	1
8121031	14-09-2022 08:52	14-09-2022 09:31	14-09-2022	0
8084621	15-09-2022 10:54	15-09-2022 13:50	15-09-2022	0
8123271	15-09-2022 10:56	15-09-2022 11:34	15-09-2022	1
8118971	15-09-2022 10:58	15-09-2022 14:04	15-09-2022	0
8060351	15-09-2022 11:02	15-09-2022 13:28	15-09-2022	2
8123301	15-09-2022 11:06	15-09-2022 11:31	15-09-2022	4

[ShankerV]

Hi @jeffyfj 

Can you please highlight the logic why 2 and 4 are populated in Last 2 record.

[ShankerV]

Hi @jeffyfj 

One way of doing this.

Many thanks

Shanker V

[ShankerV]

Hi @jeffyfj 

Step 1: Input

Step 2:

Step 3:

IF datetimediff([Row-1:ISO date],[ISO date],"minutes")>0
THEN 1
ELSE 0
ENDIF

Many thanks

Shanker V

[jeffyfj]

Hi Shanker

Thanks for the prompt reply. The logic for the last row is that it checks all the previous rows for created date of 15-09-2022. Accordingly, in rows 7-11, assigned datetime of rows 7,8,9 and 10 are greater than assigned datetime of last row. Hence arrival volume needs to be 4.

Thanks,

Jeffy

[jeffyfj]

Hi Shanker

Thanks for the prompt reply. The logic for the last row is that it checks all the previous rows for created date of 15-09-2022. Accordingly, in rows 7-11, assigned datetime of rows 7,8,9 and 10 are greater than assigned datetime of last row. Hence arrival volume needs to be 4.

Thanks,

Jeffy