Calculating the working time of staff on 2 tables

[krmvacar]

Hi all,

I have a 2 tables and I want to calculate the working and downtime of staff at a station.

Here, there is working time in 1 table, and in 2 tables, it is written what he did between which hours. I want to write the works that fall within the range of 1 table here, and I want to enter the range and write Break Time when there is a gap. Your help is requested

Table1:

code	StartTıme	EndTime	stationId
URT00592195	2024-03-31 23:01	2024-04-01 00:26	f0f34e49-fc6d-49cd-a675-39745da21ceb
URT00592198	2024-04-31 11:01	2024-04-31 13:35	d0f34e49-fc6d-49df-b675-39745da21ceb

Table2:

stationId	StartTıme	EndTime	Reason
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 00:05	2024-03-30 00:10	3432
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 01:00	2024-03-30 01:40	54321
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 08:04	2024-03-30 08:18	554532
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 08:18	2024-03-30 08:25	6433
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 08:25	2024-03-30 08:28	5443
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 08:44	2024-03-30 08:51	2346
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 08:51	2024-03-30 08:55	654322
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 08:55	2024-03-30 09:02	45467
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 09:02	2024-03-30 09:05	938383
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 11:54	2024-03-30 12:01	2345533
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 12:01	2024-03-30 12:08	46546
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 12:08	2024-03-30 12:15	332423
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 12:15	2024-03-30 12:37	4353453
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 12:37	2024-03-30 12:51	43675453
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 12:51	2024-03-30 12:54	234236
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 14:31	2024-03-30 14:45	982736
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 16:22	2024-03-30 17:05	21621910
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 18:33	2024-03-30 18:43	27374932
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-30 19:06	2024-03-30 19:13	102873
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-31 21:03	2024-03-31 21:14	18292
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-31 21:14	2024-03-31 21:21	890282
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-31 21:21	2024-03-31 21:36	16171911
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-31 21:52	2024-03-31 22:23	92837393
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-31 22:55	2024-03-31 23:00	8736521
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-31 23:00	2024-03-31 23:07	62625510
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-31 23:07	2024-03-31 23:19	8272801772
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-31 23:19	2024-03-31 23:43	29283667218
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-03-31 23:43	2024-03-31 23:46	83652518
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-04-01 00:18	2024-04-01 00:22	92876271900
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-04-01 00:22	2024-04-01 00:25	3763516111
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-04-01 00:25	2024-04-01 00:33	11111887733
f0f34e49-fc6d-49cd-a675-39745da21ceb	2024-04-01 00:33	2024-04-01 00:42	88777336565
d0f34e49-fc6d-49df-b675-39745da21ceb	2024-04-31 09:41	2024-04-31 11:03	90993338
d0f34e49-fc6d-49df-b675-39745da21ceb	2024-04-31 11:41	2024-04-31 12:43	909933354
d0f34e49-fc6d-49df-b675-39745da21ceb	2024-04-31 12:58	2024-04-31 13:31	9099333432
d0f34e49-fc6d-49df-b675-39745da21ceb	2024-04-31 13:31	2024-04-31 13:43	984772229

I want To Result:

URT00592195	202403-31	6	62625510
URT00592195	202403-31	12	8272801772
URT00592195	202403-31	24	29283667218
URT00592195	202403-31	3	83652518
URT00592195	202403-31	14	Break
URT00592195	2024-04-01	18	Break
URT00592195	2024-04-01	4	92876271900
URT00592195	2024-04-01	3	3763516111
URT00592195	2024-04-01	1	11111887733
URT00592198	2024-04-31	2	90993338
URT00592198	2024-04-31	28	Break
URT00592198	2024-04-31	62	909933354
URT00592198	2024-04-31	15	Break
URT00592198	2024-04-31	33	9099333432
URT00592198	2024-04-31	4	984772229

[tresesco]

@krmvacar 
Without spending much time, first thing comes in mind intervalmatch(), have you tried that?

[krmvacar]

Hi @tresesco 

yes I tried but failed

[tresesco]

StationId is same for both codes? 

[krmvacar]

Yes same 

[tresesco]

@krmvacar , I will look at it when possible. Thanks

[krmvacar]

Thank you so much, I'm waiting

[vincent_ardiet_]

What I see is that you first need to add the previous endtime in table2 with proper sorting and testing if this is the same station. This will give you something to use for the breaks.
Create a mapping table from table1 with a key composed of code+startime+stationid and endtime as a value.
Then, with intervalmatch linking table1 (starttime) and table2 (starttime and endtime). 
You can then concatenate to the result a second intervalmatch use where you have linked table1 (starttime) and table2 (previousendtime and starttime) tagging this as "Break" in the Reason field. 
Here you can use the mapping table to take the minimum between the endtime of table2 and the applymap (as for example, the last line in table2 should not be counted until 13:43 but 13:35).
You should be able to compute your result. 

[krmvacar]

Thank you so much I solve