Calculating Absences by hours and days between dates

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 .

I have provided some sample data below of what I would expect:

dteStartDateTime	dteEndDateTime	Working Time Lost
27/02/2024 08:30	27/02/2024 16:30	1
11/03/2024 08:30	11/03/2024 16:30	1
12/03/2024 08:30	12/03/2024 16:30	1
13/03/2024 08:30	15/03/2024 10:45	2.28125
13/11/2023 11:05	13/11/2023 17:15	0.6775
31/01/2024 14:15	31/01/2024 16:30	0.28125

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.

Solution

The following seems to overcome my issue of the partial day absence on the last day. Comment included:

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)