SQL Server - Null row not Showing?

Question

Is there a way how to add null values rows, replacing with 0?

Ideally,I want to show data of all Month, if there is no data then count column set to zero.

Currently SQL query is not Returning Empty Rows and I'm getting output like this...

I'm using the code below to get the result set below that:

        SELECT  

        DATENAME(MONTH,A.Date) 'Month', COUNT(LateStatus) 'Count', 'Late' 'Type'

        FROM    

        Attendance1 A

        WHERE   

        A.EnrollId = '10000' AND A.Date BETWEEN '2016-01-01' AND '2016-08-31' AND LateStatus = 'L'

        GROUP BY 

        DATENAME(MONTH,A.Date)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Use a recursive CTE (dynamically makes a calendar table):

With Months(mon) As 
    (Select dateAdd(month, datediff(month, 365, getdate()), 0) 
     Union All 
     Select DateAdd(month, 1, mon) from Months
     Where mon < getdate())
Select DATENAME(MONTH, m.mon ) 'Month', 
     COUNT(LateStatus) 'Count', 'Late' 'Type'
FROM months m 
     left join Attendance1 a
         on a.Date between m.mon and dateadd(month, 1, m.mon)
             and a.EnrollId = '10000' 
             and a.LateStatus = 'L'
GROUP BY DATENAME(MONTH, m.Mon)

SQL Server - Null row not Showing?

Sign up