I have attached a piece of code which I use to calculate students start_time, end_time, time late, time absent and so on for each day. My aim is to find students who have been absent for longer than 4 hours per day (which is why I write all the records into the temp table #students then select from here which students have been absent 4 hours). My problem is because absent is a varchar it wont let me compare to see if absent > 4 as I get the following error
Msg 8114, Level 16, State 5, Line 80
Error converting data type varchar to numeric.
Can anyone help me fix this?
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'
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'
GROUP BY student_id, Date, week_no
where Absent > 4