Untitled

WITH ScheduleData AS (
    SELECT 
        tt.LName,
        tt.FName,
        tt.teacherID,
        'Monday' AS day_of_week,
        ICS.MondaySchedule AS schedule,
        ICS.MondayTeacherID AS teacher_id
    FROM tblteachers tt
    JOIN ISP_Cloud_Class_Schedule ICS ON tt.teacherID = ICS.MondayTeacherID
    
    UNION ALL
    
    SELECT 
        tt.LName,
        tt.FName,
        tt.teacherID,
        'Tuesday' AS day_of_week,
        ICS.TuesdaySchedule AS schedule,
        ICS.TuesdayTeacherID AS teacher_id
    FROM tblteachers tt
    JOIN ISP_Cloud_Class_Schedule ICS ON tt.teacherID = ICS.TuesdayTeacherID
    
    UNION ALL
    
    SELECT 
        tt.LName,
        tt.FName,
        tt.teacherID,
        'Wednesday' AS day_of_week,
        ICS.WednesdaySchedule AS schedule,
        ICS.WednesdayTeacherID AS teacher_id
    FROM tblteachers tt
    JOIN ISP_Cloud_Class_Schedule ICS ON tt.teacherID = ICS.WednesdayTeacherID
    
    UNION ALL
    
    SELECT 
        tt.LName,
        tt.FName,
        tt.teacherID,
        'Thursday' AS day_of_week,
        ICS.ThursdaySchedule AS schedule,
        ICS.ThursdayTeacherID AS teacher_id
    FROM tblteachers tt
    JOIN ISP_Cloud_Class_Schedule ICS ON tt.teacherID = ICS.ThursdayTeacherID
    
    UNION ALL
    
    SELECT 
        tt.LName,
        tt.FName,
        tt.teacherID,
        'Friday' AS day_of_week,
        ICS.FridaySchedule AS schedule,
        ICS.FridayTeacherID AS teacher_id
    FROM tblteachers tt
    JOIN ISP_Cloud_Class_Schedule ICS ON tt.teacherID = ICS.FridayTeacherID
)

, DailyTotalHours AS (
    SELECT 
        LName,
        FName,
        teacherID,
        SUM(
            CASE 
                WHEN schedule = '-' THEN 0  -- Replace "-" with 0
                ELSE 
                    ISNULL(
                        DATEDIFF(MINUTE, 
                            TRY_CAST(SUBSTRING(schedule, 1, 5) AS TIME), -- Safely attempt conversion
                            TRY_CAST(SUBSTRING(schedule, 9, 5) AS TIME)
                        ), 0
                    )
            END
        ) / 60.0 AS total_hours_per_day
    FROM ScheduleData
    GROUP BY LName, FName, teacherID, day_of_week
)

SELECT 
    sd.LName,
    sd.FName,
    sd.teacherID,
    SUM(dth.total_hours_per_day) AS total_hours_worked_for_week,
    ICS.MondaySchedule AS Monday,
    ICS.TuesdaySchedule AS Tuesday,
    ICS.WednesdaySchedule AS Wednesday,
    ICS.ThursdaySchedule AS Thursday,
    ICS.FridaySchedule AS Friday
FROM ScheduleData sd
JOIN DailyTotalHours dth ON sd.LName = dth.LName AND sd.FName = dth.FName AND sd.teacherID = dth.teacherID
JOIN ISP_Cloud_Class_Schedule ICS ON sd.teacher_id = ICS.MondayTeacherID -- Use MondayTeacherID to get Monday schedule
GROUP BY sd.LName, sd.FName, sd.teacherID, ICS.MondaySchedule, ICS.TuesdaySchedule, ICS.WednesdaySchedule, ICS.ThursdaySchedule, ICS.FridaySchedule
ORDER BY sd.teacherID;