Combine rows if they are during the same day

[Travis_Ratliff]

Start Time	Stop Time	Volume
2024/08/02 06:00	2024/08/02 22:00	400
2024/08/03 06:00	2024/08/03 22:00	397
2024/08/08 00:00	2024/08/08 23:00	575
2024/08/08 23:00	2024/08/09 00:00	25
2024/08/09 00:00	2024/08/10 23:00	775
2024/08/10 23:00	2024/08/11 00:00	25
2024/08/20 00:00	2024/08/20 23:00	255
2024/08/20 06:00	2024/08/20 22:00	160
2024/08/20 23:00	2024/08/21 00:00	25
2024/08/21 00:00	2024/08/21 23:00	250
2024/08/21 06:00	2024/08/21 22:00	220
2024/08/21 07:00	2024/08/21 14:00	105
2024/08/21 23:00	2024/08/22 00:00	25
2024/08/22 00:00	2024/08/22 23:00	535
2024/08/22 13:00	2024/08/22 17:00	40
2024/08/22 23:00	2024/08/23 00:00	25
2024/08/23 00:00	2024/08/24 23:00	640
2024/08/23 14:00	2024/08/24 19:00	205
2024/08/24 23:00	2024/08/25 00:00	30
2024/08/25 00:00	2024/08/26 23:00	950
2024/08/25 13:00	2024/08/25 19:00	150
2024/08/26 23:00	2024/08/27 00:00	40

I am trying to combine the volumes based on if their times were ever going at the same time. Currently I am subtracting one day from all of the Stop times that contain "00:00" and then grouping on that new date and then summing everything together. This works for all of them except the bottom three rows (950, 150, and 40) where it will combine the 950 and 40 because they would have a stop time of 2024/08/26 but I can't get it to coming the 150.

Any thoughts on how to accomplish this or if it is possible would be appreciated. 

[binuacs]

@Travis_Ratliff can you post the expected result?

[DataNath]

@Travis_Ratliff I'm not sure if I'm missing something here or if there's any extra logic you can add (as it looks stop times are in some sort based on Stop Time's day ascending), but the 150 value belongs to 2024/08/25 so how would you want to force that to 2024/08/26?

[Travis_Ratliff]

@binuacs Expected result would be 

Volume	Which volumes got combined
400	None
397	None
600	575 and 25
800	775 and 25
440	255, 160, and 25
600	250, 220,105, and 25
600	535, 40, and 25
875	640, 205, and 30
1140	950, 40, and 150

[TUSHAR050392]

Hey @Travis_Ratliff 

I think you need to do some data transformation since you need to sum data by start date and stop date. Please follow below steps -

1. Create two new columns using below formula and make them date data type

Start Date - datetimeparse(Start Time, "%y-%m-%d")

Stop Date - IF RIGHT(Stop Time,5) =  '00:00' THEN DATETIMEADD(datetimeparse(Stop Time, "%y-%m-%d")),-1,'days') else datetimeparse(Stop Time, "%y-%m-%d") endif

2. Once these two columns are created, use a summarize tool group by Start and Stop Date and sum volume.

This will give you the intended result. Hope this helps.

[Travis_Ratliff]

@DataNath Essentially what I want is if two lines overlap at all then combine them. So the 950 and 150 would combine because they overlap on the 25th and then that would combine with the 40 because it overlaps on the 26th. 

An easy example of this would be for 8/20 where it would combine 255, 160, and 25 because they are all on 8/20. The hard part is because the 950, 150, and 40 are across two different days so not sure how to combine them.

[DataNath · Accepted solution]

@Travis_Ratliff got it. Do you want to give this a try? I've got your example data set working but always worth testing with a more extensive size. Especially as I'm at the end of a long day!