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