The following query allows me to calculate in hours (if less than a day – converted to the portion of the day) or days (depending on the length absent), the difference between two dates:

Within the absences table, absences can be recorded as either 00:00 to 23:59, 08:30 to 16:30 (full working day) or a few hours in the day and sometimes even past the working day, examples would be 13:00 to 17:15. For my part I only want to calculate the portion up-until 16:30.

The issue I have with the query below is where the absence spans over a day and the end date is a portion of that day.

A working day is 8 hours (08:30 to 16:30).

For example dteStartDate = 13/04/2024 08:30 and dteEndDAte 15/04/2024 10:45

The query below gives me 3 working days for the example when the return I would like is 2.268 .

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 in multi-day absences
            (DATEDIFF(DAY, A.dteStartDateTime, A.dteEndDateTime) + 1)
            - (DATEDIFF(WEEK, A.dteStartDateTime, A.dteEndDateTime) * 2)
            
    END AS [Working Time Lost]

FROM TblCoverManagerAbsences A
JOIN TblCoverManagerAbsencesReasons AR ON A.intReason = AR.TblCoverManagerAbsencesReasonsId

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)

I’m not sure how I can incorporate this with having a secondary table of dates to lookup against and check each date for the date range.