Расчет отсутствия по часам и дням между датами

Спасибо, что дали мне возможность изучить функцию SQL Server:

Решение в SQL Server:

with T as (
  select *,
    DATEDIFF(day, dtestartdatetime, dteenddatetime) AS dateDiff,
    ((DATEPART(HOUR, dtestartdatetime)*3600) + (DATEPART(MINUTE, dtestartdatetime)*60) + DATEPART(SECOND, dtestartdatetime)) as loginTime_sec,
    ((DATEPART(HOUR, dteenddatetime)*3600) + (DATEPART(MINUTE, dteenddatetime)*60) + DATEPART(SECOND, dteenddatetime)) as logoutTime_sec
  from dailyLog
),
T0 as (
  SELECT
  cast('08:30:00' as TIME) as officialEntryTime,
  cast('16:30:00' as TIME) as officialExitTime
),
T1 as (
  SELECT
      ((DATEPART(HOUR, officialEntryTime)*3600) + (DATEPART(MINUTE, officialEntryTime)*60) + DATEPART(SECOND, officialEntryTime)) as officialLoginTime_sec,
      ((DATEPART(HOUR, officialExitTime)*3600) + (DATEPART(MINUTE, officialExitTime)*60) + DATEPART(SECOND, officialExitTime)) as officialLogoutTime_sec,
      (((DATEPART(HOUR, officialExitTime)*3600) + (DATEPART(MINUTE, officialExitTime)*60) + DATEPART(SECOND, officialExitTime)) -
      ((DATEPART(HOUR, officialEntryTime)*3600) + (DATEPART(MINUTE, officialEntryTime)*60) + DATEPART(SECOND, officialEntryTime))) as officialTotalTime
  From T0
),
T2 as (
  select T.dtestartdatetime, 
    T.dteenddatetime,
    ROUND(CAST(
     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)
    END AS REAL) / CAST(((dateDiff+1) * T1.officialTotalTime) AS REAL), 4) as Working_Time_Lost
  from T, T1
)
select * from T2;

Пример вывода (SQL Server): db<>fiddle
Пример вывода (MySQL): db<>fiddle

Вывод:

Спасибо !!!

Следующее, кажется, решает мою проблему частичного отсутствия в последний день. Комментарий включен:

SELECT 
dteStartDateTime,
dteEndDateTime,
    CASE 
        WHEN DATEDIFF(MINUTE, dteStartDateTime, dteEndDateTime) <= 1440 THEN 
            CAST(DATEDIFF(MINUTE, 
                          CASE 
                              WHEN CAST(dteStartDateTime AS time) < '08:30' THEN CAST(CAST(dteStartDateTime AS date) AS datetime) + CAST('08:30' AS datetime)
                              ELSE dteStartDateTime 
                          END, 
                          CASE 
                              WHEN CAST(dteEndDateTime AS time) > '16:30' THEN CAST(CAST(dteEndDateTime AS date) AS datetime) + CAST('16:30' AS datetime)
                              ELSE dteEndDateTime 
                          END
                         )/60.0 AS decimal(10,2))/8.0
        ELSE 
            -- Adjusting for weekends and partial days in multi-day absences
            (DATEDIFF(DAY, dteStartDateTime, dteEndDateTime) + 1)
            - (DATEDIFF(WEEK, dteStartDateTime, dteEndDateTime) * 2)
            - (CASE 
                  WHEN CAST(dteEndDateTime AS time) < '16:30' THEN 1 - CAST(DATEDIFF(MINUTE, CAST(CAST(dteEndDateTime AS date) AS datetime) + CAST('08:30' AS datetime), dteEndDateTime)/60.0 AS decimal(10,2))/8.0
                  ELSE 0
               END)
    END AS [Working Time Lost]

FROM TblCoverManagerAbsences A
JOIN TblCoverManagerAbsencesReasons AR ON A.intReason = AR.TblCoverManagerAbsencesReasonsId
JOIN TblStaff S ON A.txtTeacher = S.User_Code

OUTER APPLY
(
    SELECT Min(txtStartDate) AS StartDate
    FROM TblSchoolManagementTermDates
    WHERE intSchoolYear = CASE 
                              WHEN MONTH(getdate()) BETWEEN 9 AND 12 THEN YEAR(getdate()) 
                              ELSE YEAR(getdate()) - 1 
                          END
) AS StartDate

WHERE 
    AR.txtName = 'Illness' 
    AND CONVERT(date, dteStartDateTime) >= CONVERT(date, StartDate.StartDate)
    AND CONVERT(date, dteStartDateTime) <= DATEADD(week, DATEDIFF(week, 0, GETDATE()) - 1, 6)