Untitled

SELECT 
    LName,
    FName,
    tt.teacherID,
    RIGHT( CAST(total_minutes / 60 AS NVARCHAR(2)), 2) + ':' + RIGHT('0' + CAST(total_minutes % 60 AS NVARCHAR(2)), 2) AS total_hours_worked_per_week,
    (SELECT STUFF(
            (SELECT ', ' + cs.MondaySchedule
             FROM ISP_Cloud_Class_Schedule cs
             WHERE cs.MondayTeacherID = tt.teacherID
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
     ) AS Monday,
    (SELECT STUFF(
            (SELECT ', ' + cs.TuesdaySchedule
             FROM ISP_Cloud_Class_Schedule cs
             WHERE cs.TuesdayTeacherID = tt.teacherID
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
     ) AS Tuesday,
    (SELECT STUFF(
            (SELECT ', ' + cs.WednesdaySchedule
             FROM ISP_Cloud_Class_Schedule cs
             WHERE cs.WednesdayTeacherID = tt.teacherID
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
     ) AS Wednesday,
    (SELECT STUFF(
            (SELECT ', ' + cs.ThursdaySchedule
             FROM ISP_Cloud_Class_Schedule cs
             WHERE cs.ThursdayTeacherID = tt.teacherID
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
     ) AS Thursday,
    (SELECT STUFF(
            (SELECT ', ' + cs.FridaySchedule
             FROM ISP_Cloud_Class_Schedule cs
             WHERE cs.FridayTeacherID = tt.teacherID
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
     ) AS Friday
FROM (
    SELECT 
        tt.teacherID,
        SUM(
            CASE
                WHEN cs.MondaySchedule IS NOT NULL THEN
                    DATEDIFF(MINUTE, 
                        TRY_CAST(REPLACE(SUBSTRING(cs.MondaySchedule, 1, 5), '-', '00:00') AS TIME),
                        TRY_CAST(REPLACE(SUBSTRING(cs.MondaySchedule, 9, 5), '-', '00:00') AS TIME)
                    )
                ELSE 0
            END
        ) +
        SUM(
            CASE
                WHEN cs.TuesdaySchedule IS NOT NULL THEN
                    DATEDIFF(MINUTE, 
                        TRY_CAST(REPLACE(SUBSTRING(cs.TuesdaySchedule, 1, 5), '-', '00:00') AS TIME),
                        TRY_CAST(REPLACE(SUBSTRING(cs.TuesdaySchedule, 9, 5), '-', '00:00') AS TIME)
                    )
                ELSE 0
            END
        ) +
        SUM(
            CASE
                WHEN cs.WednesdaySchedule IS NOT NULL THEN
                    DATEDIFF(MINUTE, 
                        TRY_CAST(REPLACE(SUBSTRING(cs.WednesdaySchedule, 1, 5), '-', '00:00') AS TIME),
                        TRY_CAST(REPLACE(SUBSTRING(cs.WednesdaySchedule, 9, 5), '-', '00:00') AS TIME)
                    )
                ELSE 0
            END
        ) +
        SUM(
            CASE
                WHEN cs.ThursdaySchedule IS NOT NULL THEN
                    DATEDIFF(MINUTE, 
                        TRY_CAST(REPLACE(SUBSTRING(cs.ThursdaySchedule, 1, 5), '-', '00:00') AS TIME),
                        TRY_CAST(REPLACE(SUBSTRING(cs.ThursdaySchedule, 9, 5), '-', '00:00') AS TIME)
                    )
                ELSE 0
            END
        ) +
        SUM(
            CASE
                WHEN cs.FridaySchedule IS NOT NULL THEN
                    DATEDIFF(MINUTE, 
                        TRY_CAST(REPLACE(SUBSTRING(cs.FridaySchedule, 1, 5), '-', '00:00') AS TIME),
                        TRY_CAST(REPLACE(SUBSTRING(cs.FridaySchedule, 9, 5), '-', '00:00') AS TIME)
                    )
                ELSE 0
            END
        ) AS total_minutes
    FROM tblteachers tt
    JOIN ISP_Cloud_Class_Schedule cs ON tt.teacherID = cs.MondayTeacherID
    GROUP BY tt.teacherID
) AS TotalMinutesQuery
JOIN tblteachers tt ON TotalMinutesQuery.teacherID = tt.teacherID
ORDER BY tt.teacherID;