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!

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.

SQLFiddle