Muhammad Qasim Ashraf Muhammad Qasim Ashraf - 3 months ago 10
SQL Question

SQL Server - Null row not Showing?

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...

enter image description here

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)

Answer

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)