# counting and grouping in sql based on a values

Hi experts need to amend the code I have to cater for one more thing and I am really stuck as to how to proceed - so im hoping someone can help me

I have attached a spreadsheet which shows the classes that one student attended for one day and also the marks made against that student for the one day.

In the code below I am calculating the time_absent where attendance_type = 'AA' or 'AB' but I need to change this to calculate the time_absent based on each different descriptions in the description field (shown in spreadsheet).  Using the example from the spreadsheet I need two different descriptions being authorised absence and class cancelled and I need a seperate line for each description.

Help Appreciated example-output.xls
``````SELECT acad_period, student_id, student_name, student_surname, stage_code,
course_code, course_period, course_desc, dept_code, dept_desc,
campus_id, campus_desc,
date, week_no, [start_time], [end_time],
convert(varchar, convert(int,floor([Total Hours])))+':'+
replicate('0',(2 - len(convert (varchar, convert(int,([Total Hours] - floor([Total Hours])) * 60.0)))))
+convert (varchar, convert(int,([Total Hours] - floor([Total Hours])) * 60.0)) as total_hours,
(CASE WHEN [total breaks]  < 0 THEN '-' ELSE '' END) + convert(varchar, convert(int,ABS([total breaks] -([total breaks] %1))))+':'+
RIGHT(('0'+convert(varchar,  convert(int,60*ABS([total breaks] %1)))),2) as total_breaks,
CONVERT(VARCHAR(5), [Late], 108) AS late,
CONVERT(VARCHAR(5), [Absent], 108) AS time_absent,
CONVERT(VARCHAR(5), [Unmarked], 108) AS not_marked,
CONVERT(VARCHAR(5), [Present], 108) AS time_present

FROM
(
SELECT  acad_period, student_id, student_name, student_surname, stage_code,
course_code, course_period, course_desc, dept_code, dept_desc,
campus_id, campus_desc,
Date,week_no,
MIN(CONVERT(datetime, start_time)) AS 'start_time',
MAX(CONVERT(datetime, End_Time)) AS 'end_time',
ROUND(cast((datediff(mi, MIN(start_time), MAX(end_time))  / 60.0) as FLOAT),2) AS 'Total Hours',

(DATEDIFF(minute, MIN(start_time), MAX(end_time)) - SUM(DATEDIFF(minute, start_time, end_time))) / 60.0 AS 'Total Breaks',

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), 0) AS 'Absent',
DateAdd(n, SUM(CASE WHEN attendance_type IS NULL THEN DATEDIFF(minute, start_time,  end_time) ELSE 0 END), 0) AS 'Unmarked',
DateAdd(n, SUM(CASE WHEN attendance_type IN ('PR', 'LA') THEN DATEDIFF(minute, start_time,  end_time) ELSE 0 END)
- SUM(CASE WHEN attendance_type = 'LA' THEN late_minutes ELSE 0 END), 0) AS 'Present'

FROM stud_table
GROUP BY acad_period, student_id, student_name, student_surname, stage_code,
course_code, course_period, course_desc, dept_code, dept_desc,
campus_id, campus_desc, Date, week_no
) X
``````
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Last Comment
lisa_mc

8/22/2022 - Mon