I have a challenge to report on attendance for students per unique week per day for a given date range. So I currently can extract weekly attendance per day but it summed (aggregated) over the days of the week, so that if there were 4 weeks and 4 Mondays (ie. 1 Monday per week) I get the 4 Mondays but not shown per week.
My current tables involved:
Attendance Table
---------------------
Attendance_Identifier
Student_Identifier
Classroom_Identifier
Attendance_Datetime
Attendance_Value
...
Student
------------------
Student_Identifier
Person_Identifier
Classroom_Identifier
...
Person
-----------------
Person_Identifier
Frist_Name
Last_Name
Gender
...
My current sql (given a date range of '2017-06-25' and '2017-07-20' and a given class of '365'):
SELECT
concat(p.Frist_Name, ' ',p.Last_Name) AS Student, p.Gender
,cast(COUNT(CASE WHEN weekday(date(Attendance_Datetime)) = 0
then 1 END) AS CHAR) AS Mon
,cast(COUNT(CASE WHEN weekday(date(Attendance_Datetime)) = 1
then 1 END) AS CHAR) AS Tue
,cast(COUNT(CASE WHEN weekday(date(Attendance_Datetime)) = 2
then 1 END) AS CHAR) AS Wed
,cast(COUNT(CASE WHEN weekday(date(Attendance_Datetime)) = 3
then 1 END) AS CHAR) AS Thu
,cast(COUNT(CASE WHEN weekday(date(Attendance_Datetime)) = 4
then 1 END) AS CHAR) AS Fri
,cast(COUNT(distinct date(Attendance_Datetime)) AS CHAR) AS WeeklyTotal
FROM (
SELECT distinct date(Attendance_Datetime) as Attendance_Date,a.*
FROM Attendance a WHERE date(Attendance_Datetime) BETWEEN '2017-06-25' AND '2017-07-20'
AND a.Classroom_Identifier = 365 AND a.Course_Identifier IS NULL
AND (Attendance_Value = 'Present' OR Attendance_Value = 'Late')
AND weekday(date(Attendance_Datetime)) not in (5,6) GROUP BY Student_Identifier,Attendance_Date
) a
JOIN Student s ON s.Student_Identifier=a.Student_Identifier
JOIN Person p ON p.Person_Identifier=s.Person_Identifier
WHERE (Attendance_Value = 'Present' OR Attendance_Value = 'Late')
AND (p.Gender = 'Male' OR p.Gender = 'Female')
GROUP BY Student, Gender ORDER BY p.Gender, Student, Mon DESC;
