SQL How to fill with zero for days not found

Question

I have the following SQL Query:

SELECT SUM(data.frequency) as total,
data.day as day, 
data.weekday,
CASE data.weekday 
WHEN 1 THEN 'SUN'
WHEN 2 THEN 'MON'
WHEN 3 THEN 'TUE'
WHEN 4 THEN 'WED'
WHEN 5 THEN 'THU'
WHEN 6 THEN 'FRI'
WHEN 7 THEN 'SAT'
END as weekday
FROM (
SELECT COUNT(createdate) as frequency,
DATEPART(day,createdate) as day, 
DATEPART(weekday,createdate) as weekday,
createdate as createddate
FROM requests ma
WHERE createdate BETWEEN 
@startdate
AND
@enddate

GROUP BY datepart(day,createdate),DATEPART(weekday,createdate),createdate

) data
GROUP BY data.day,data.weekday, CASE data.weekday 
WHEN 1 THEN 'SUN'
WHEN 2 THEN 'MON'
WHEN 3 THEN 'TUE'
WHEN 4 THEN 'WED'
WHEN 5 THEN 'THU'
WHEN 6 THEN 'FRI'
WHEN 7 THEN 'SAT'
END
order by day

This will return a table like this:

This is the ideal, but I can get queries where a day or more days are missing and then I would like to fill the missing row with the day, weekday and weekdaylabel and set for the total column the value 0.

Any clue?

Possible duplicate of [SQL Server: How to select all days in a date range even if no data exists for some days](http://stackoverflow.com/questions/5899829/sql-server-how-to-select-all-days-in-a-date-range-even-if-no-data-exists-for-so) — Tab Alleman, Aug 12 '16 at 20:18
Here's another question with a cool approach: http://stackoverflow.com/questions/3365697/add-empty-row-to-query-results-if-no-results-found — Tab Alleman, Aug 12 '16 at 20:20
@TabAlleman I must be missing something how does the second link you provided relate to this situation? — Matt, Aug 12 '16 at 20:32

score 0 · Answer 1 · answered Aug 12 '16 at 20:27

DECLARE @startdate DATE = '1/1/2016'
DECLARE @enddate DATE = '2/1/2016'

;WITH cteDates AS (
    SELECT @startdate AS [Date]
    UNION ALL
    SELECT
       DATEADD(day,1,Date) AS [Date]
    FROM
       cteDates c
    WHERE
       c.Date <= @enddate
)

SELECT
    d.[Date]
    ,COUNT(*) as frequency
    ,DAY(d.[Date]) as [day]
    ,DATEPART(weekday,d.[Date]) as [weekday]
    ,LEFT(DATENAME(dw,d.[Date]),3) as weekdaylabel 
FROM
    cteDates d
    LEFT JOIN requests ma
    ON d.[Date] = ma.createdate
GROUP BY
    d.[Date]
ORDER BY
    [day]

Use a recursive cte to build a date table with all of the dates you want and then simply left join your requests table and do you count() aggregation.

You can use a couple of easier methods to get to the same information you want by using different functions. When you use those function because they are built on [date] you will not have to include them in the group by as well.

SQL How to fill with zero for days not found

1 Answers1