Skip to content
Advertisement

Display records that are not present in a table and a given date range

Let’s say I have two tables – Student and Student Attendance tables. The Student table displays basic information about the student:

Student# FirstName LastName
201710 John Smith
201711 John Doe
201712 Anna Sy
201713 Chris Dy

While the Student Attendance table displays the TimeIn and TimeOut of the student from class:

Student# Date TimeIn TimeOut
201710 2016-01-06 00:09:00.000 00:15:00.000
201711 2016-01-06 00:09:10.000 00:15:00.000
201712 2016-01-06 00:09:05.000 00:15:00.000
201713 2016-01-06 00:09:00.000 00:15:00.000
201710 2016-01-07 00:09:00.000 00:15:00.000
201711 2016-01-07 00:09:10.000 00:15:00.000
201712 2016-01-07 00:09:05.000 00:15:00.000
201710 2016-01-08 00:09:00.000 00:15:00.000
201712 2016-01-08 00:09:10.000 00:15:00.000
201713 2016-01-08 00:09:05.000 00:15:00.000

My objective is to also include the Student #s of the students who do not have logs for that day, which I will use to determine that they are absent.

Student# Date TimeIn TimeOut
201710 2016-01-06 00:09:00.000 00:15:00.000
201711 2016-01-06 00:09:10.000 00:15:00.000
201712 2016-01-06 00:09:05.000 00:15:00.000
201713 2016-01-06 00:09:00.000 00:15:00.000
201710 2016-01-07 00:09:00.000 00:15:00.000
201711 2016-01-07 00:09:10.000 00:15:00.000
201712 2016-01-07 00:09:05.000 00:15:00.000
201713 NULL NULL NULL
201710 2016-01-08 00:09:00.000 00:15:00.000
201711 NULL NULL NULL
201712 2016-01-08 00:09:10.000 00:15:00.000
201713 2016-01-08 00:09:05.000 00:15:00.000

Appreciate the help!

Advertisement

Answer

First you need to create a Calendar table to populate the required date, and then CROSS JOIN it with the Students table.

After that, left join the StudentsAttendance table with the result set.

Calendar table:

This table hold the below dates.

Now execute the query

Try this, and let us know if you are getting any error.

SQLFiddle

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement