How to calculate daily in out working hours

I have a table that stores daily in-out employee-wise data. I will share one employee's data below.

NO, EMP ID      date                IN-OUT
1   MS00093 12-Jun-24 10:43:27 AM   0
2   MS00093 12-Jun-24 10:50:18 AM   1
3   MS00093 12-Jun-24 1:20:08 PM    1
4   MS00093 12-Jun-24 1:20:27 PM    0
5   MS00093 12-Jun-24 1:21:08 PM    1
6   MS00093 12-Jun-24 1:21:16 PM    0
7   MS00093 12-Jun-24 1:30:13 PM    1
8   MS00093 12-Jun-24 1:56:05 PM    0
9   MS00093 12-Jun-24 2:38:34 PM    1
10  MS00093 12-Jun-24 2:38:34 PM    1
11  MS00093 12-Jun-24 2:40:21 PM    0
12  MS00093 12-Jun-24 2:40:21 PM    0
13  MS00093 12-Jun-24 2:42:14 PM    1
14  MS00093 12-Jun-24 2:42:14 PM    1
15  MS00093 12-Jun-24 2:42:40 PM    1
16  MS00093 12-Jun-24 3:08:05 PM    0
17  MS00093 12-Jun-24 3:08:05 PM    0
18  MS00093 12-Jun-24 3:10:22 PM    1
19  MS00093 12-Jun-24 3:12:40 PM    1
20  MS00093 12-Jun-24 3:15:27 PM    0
21  MS00093 12-Jun-24 3:36:38 PM    0
22  MS00093 12-Jun-24 3:38:36 PM    1
23  MS00093 12-Jun-24 3:38:55 PM    1
24  MS00093 12-Jun-24 4:29:57 PM    1
25  MS00093 12-Jun-24 5:12:17 PM    1

In the provided data, "0" represents the time an employee checked IN and "1" represents the time they checked OUT. To calculate the sum of the last check-in and the first check-out, review rows 1 -2,4-5,8-9,12 -13,17-18,21-22 for accurate check-in and check-out times. If an employee's data includes multiple entries, consider the last check-in, as seen in rows 12, 17, and 21. For multiple check-outs, consider the first check-in, such as in rows 2, 9, 13, 18, and 22. Once this is verified, calculate the total hours worked.

expected Output

NO  EMP ID    DATE                IN-OUT sum of Hour
1   MS00093 12-Jun-24 10:43:27 AM   0   06 mi 51 sec
2   MS00093 12-Jun-24 10:50:18 AM   1   
3   MS00093 12-Jun-24 1:20:08 PM    1   
4   MS00093 12-Jun-24 1:20:27 PM    0   41 sec
5   MS00093 12-Jun-24 1:21:08 PM    1   
6   MS00093 12-Jun-24 1:21:16 PM    0   08 mi 57sec
7   MS00093 12-Jun-24 1:30:13 PM    1   
8   MS00093 12-Jun-24 1:56:05 PM    0   42 mi 29sec
9   MS00093 12-Jun-24 2:38:34 PM    1   
10  MS00093 12-Jun-24 2:38:34 PM    1   
11  MS00093 12-Jun-24 2:40:21 PM    0   
12  MS00093 12-Jun-24 2:40:21 PM    0   01mi 53 sec
13  MS00093 12-Jun-24 2:42:14 PM    1   
14  MS00093 12-Jun-24 2:42:14 PM    1   
15  MS00093 12-Jun-24 2:42:40 PM    1   
16  MS00093 12-Jun-24 3:08:05 PM    0   
17  MS00093 12-Jun-24 3:08:05 PM    0   2.17 sec
18  MS00093 12-Jun-24 3:10:22 PM    1   
19  MS00093 12-Jun-24 3:12:40 PM    1   
20  MS00093 12-Jun-24 3:15:27 PM    0   
21  MS00093 12-Jun-24 3:36:38 PM    0   2 sec
22  MS00093 12-Jun-24 3:38:36 PM    1   
23  MS00093 12-Jun-24 3:38:55 PM    1   
24  MS00093 12-Jun-24 4:29:57 PM    1   
25  MS00093 12-Jun-24 5:12:17 PM    1

i'm making a script using lag and lead functions, but I'm not getting the desired output.

select SZEMPID,SZDT,SZINOUT,LAG_DAY from (
select a.*, lag(SZINOUT) over( order by SZDT) lag_day from temp_pmp a)
where SZINOUT <>LAG_DAY ;

Solution

You can use SQL for Pattern Matching:

WITH t as (
   select NO, EMP_ID, check_DATE, last_check_in, first_check_out
   from EMP
   MATCH_RECOGNIZE (
      PARTITION BY EMP_ID
      ORDER BY NO -- or ORDER BY check_DATE
      MEASURES 
         LAST(check_in.check_DATE) as last_check_in,
         FIRST(check_out.check_DATE) as first_check_out
      ALL ROWS PER MATCH
      PATTERN (check_in+ check_out+)
      DEFINE 
         check_in AS IN_OUT = 0,
         check_out AS IN_OUT = 1
   )
select NO, EMP_ID, check_DATE, (first_check_out - last_check_in) * 24 as sum_of_Hour
from t;

(not tested)