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:
CREATE TABLE #Cal(AttDate DATE)
DECLARE @MinDate DATE = '2016-01-06', @MaxDate DATE = '2016-01-08'
WHILE @MinDate<=@MaxDate
BEGIN
INSERT INTO #Cal
SELECT @MinDate
SET @MinDate = DATEADD(DAY,1,@MinDate)
END
This table hold the below dates.
AttDate 2016-01-06 2016-01-07 2016-01-08
Now execute the query
SELECT D.Student#,D.AttDate,SA.TimeIn, SA.TimeOut
FROM(
SELECT S.Student#, AttDate
FROM #Cal C
CROSS JOIN Student S
)D
LEFT JOIN Student_Attendance SA ON SA.Student# = D.Student# AND SA.Date = D.AttDate
ORDER BY D.AttDate,D.Student#
Try this, and let us know if you are getting any error.