Filling up dummy columns in pivot result sql server

Question

I have an attendance table with the following columns:

StudentID, Date, Attendance

Now I'm running a query to get attendance results

DECLARE @paramList VARCHAR(MAX)


SET @paramList = STUFF((SELECT DISTINCT ',[' + 
CONVERT(varchar(10), [Date], 20) + ']' 
FROM AttendenceT  
WHERE Date > '2014-01-01' AND Date < '2014-01-31' 
FOR XML PATH('') ) ,1,1,'')

DECLARE @query NVARCHAR(MAX)

SET @query = 'SELECT StudentID, ' + @paramList
+ ' FROM( SELECT * FROM AttendenceT)src 
PIVOT(SUM(Attendence) FOR Date IN (' + @paramList + ')) pvt'

EXEC sp_executesql @query

The results are

The result is ok except that for missing days in attendance table, no column is available in the result. I want to modify the query so that even if date is not present in attendance table, its column is still available.

Please note that I'm not an expert at SQL. I've created this query using google and mostly stackoverflow answers of past questions.

Answer 1

First get a calendar table. Here's an SO post which touches on the subject. It doesn't matter whether this table is a permanent part or your database or a temporary table or a table variable. Let's call this table Calendar with its one column called TheDate.

Next join Calendar to the list of all students. I'll use your AttendenceT table because that's the only one you mention in your question. If you have an actual Student table that would be better because the final result will list even those students who have never attended (which I think would be useful information).

select
    s.StudentID
    ,c.TheDate
from Calendar as c
cross apply (
        select distinct StudentID from AttendenceT -- Put the Student table here if you have one
        ) as s;

Most likely you'll only want a certain data range so add a WHERE clause if needbe.

Then you can join this to your attendence table:

select
    s.StudentID
    ,c.TheDate
    ,ISNULL(a.Attendance, 0)
from Calendar as c
cross apply (
        select distinct StudentID from AttendenceT
        ) as s
left outer join AttendenceT as a
    on a.StudentID = s.StudentID
    and a.Date = c.TheDate
-- optional for a limited date range.
where c.TheDate between <your star date> and <your end date>;

This will give you every student on every date in your range, plus attendence where it exists. The ISNULL() takes care of the examples where it does not exist.

Store the output from this query into a temporary table:

select
    ...
into #TempAttendence
from ... etc.

In your posted query replaces all references to AttendenceT with #TempAttendence and you will get your required output.