MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Records: 6  Duplicates: 0  Warnings: 0

dtestartdatetime	dteenddatetime
2024-02-27 08:30:00	2024-02-27 16:30:00
2024-03-11 08:30:00	2024-03-11 16:30:00
2024-03-12 08:30:00	2024-03-12 16:30:00
2024-03-13 08:30:00	2024-03-15 10:45:00
2023-11-13 11:05:00	2023-11-13 17:15:00
2024-01-31 14:15:00	2024-01-31 16:30:00

with T as (
  select *,
    -- DAYOFWEEK(dtestartdatetime) as loginWeekDay,
    -- DAYOFWEEK(dteenddatetime) as logoutWeekDay,
    CAST(DATEDIFF(dteenddatetime, dtestartdatetime) AS SIGNED) AS dateDiff,
    CAST(TIME_TO_SEC(cast(dtestartdatetime as TIME)) AS SIGNED) as loginTime_sec,
    CAST(TIME_TO_SEC(cast(dteenddatetime as TIME)) AS SIGNED) as logoutTime_sec
  from dailyLog
),
T1 as (
  SELECT
  CAST(TIME_TO_SEC(cast('08:30:00' as TIME)) AS SIGNED) as officialLoginTime_sec,
  CAST(TIME_TO_SEC(cast('16:30:00' as TIME)) AS SIGNED) as officialLogoutTime_sec,
  CAST(TIME_TO_SEC(TIMEDIFF(cast('16:30:00' as TIME), cast('08:30:00' as TIME))) AS SIGNED) as officialTotalTime
)
select T.dtestartdatetime, 
    T.dteenddatetime,
  CASE
    WHEN(T.dateDiff = 0) THEN
        (T.logoutTime_sec-T.loginTime_sec)
    WHEN(T.dateDiff = 1) THEN
        (T.logoutTime_sec-T1.officialLogoutTime_sec) +
        (T1.officialLoginTime_sec-T.loginTime_sec)
    WHEN(T.dateDiff > 1) THEN
        (T.logoutTime_sec-T1.officialLogoutTime_sec) +
        ((T1.officialLogoutTime_sec-T1.officialLoginTime_sec) * (T.dateDiff - 1)) +
        (T1.officialLoginTime_sec-T.loginTime_sec)
    ELSE 0
  END / ((dateDiff+1) * T1.officialTotalTime) as Working_Time_Lost
from T, T1

dtestartdatetime	dteenddatetime	Working_Time_Lost
2024-02-27 08:30:00	2024-02-27 16:30:00	1.0000
2024-03-11 08:30:00	2024-03-11 16:30:00	1.0000
2024-03-12 08:30:00	2024-03-12 16:30:00	1.0000
2024-03-13 08:30:00	2024-03-15 10:45:00	0.0938
2023-11-13 11:05:00	2023-11-13 17:15:00	0.7708
2024-01-31 14:15:00	2024-01-31 16:30:00	0.2813