Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention. Check out this how-to article for more information.
if object_id('tempdb..#students','U') is not null drop table #students SELECT student_id, date, week_no, [start time], [end time], CONVERT(VARCHAR(5), [Late], 108) AS 'Late', CONVERT(VARCHAR(5), [Absent], 108) AS 'Absent' into #students FROM ( SELECT student_id, Date,week_no, MIN(CONVERT(datetime, start_time)) AS 'Start Time', MAX(CONVERT(datetime, End_Time)) AS 'End Time', DateAdd(n, SUM(CASE WHEN attendance_type = 'LA' THEN late_minutes ELSE 0 END), 0) AS 'Late', DateAdd(n, SUM(CASE WHEN attendance_type IN ('AA', 'AB') THEN DATEDIFF(minute, start_time, end_time) ELSE 0 END) + SUM(CASE WHEN attendance_type = 'LA' THEN late_minutes ELSE 0 END), 0) AS 'Absent' FROM allstudents GROUP BY student_id, Date, week_no ) X select * from #students where Absent > 4
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.