SQL - SHOW ALL DATES between two dates

Question

I have following data saved as dates in my [OccuredAtUtc] that look like this:

-- Spoiler ALERT: "2017-04-26" and "2017-04-29" are missing.

Original dates in [OccuredAtUtc]:

2017-04-24 12:16:58.5080000
2017-04-24 18:11:53.3090000
2017-04-25 18:34:18.3090000
2017-04-27 20:42:28.8570000
2017-04-28 21:10:36.7070000
2016-04-28 10:37:57.5970000
2016-04-30 10:38:55.7010000
2016-04-30 10:48:19.0390000
2016-04-31 10:48:19.2990000
.
.
.

And I have this code that returns correctly data from two intervals (previous week).

SELECT 
        [MessageType].[Name] AS [Channel],
        CONVERT(VARCHAR(11), [OccuredAtUtc], 106) AS [Time],
        COUNT(*) AS [Count]
FROM @table1
        INNER JOIN @table2 ON ... = ...
WHERE ( [OccuredAtUtc] > '2017-04-24'
        AND [OccuredAtUtc] < '2017-04-30' )
GROUP BY (CONVERT(VARCHAR(11), [OccuredAtUtc], 106)),
         [MessageType].[Name]
ORDER BY [Time] ASC

But the output won't show a row of the "26 Apr 2017" and "29 Apr 2017" because there are not records on these days in my DB.

OLD OUTPUT : with missing 26th & 29th Apr.

[Channel]       [Time]          [Count]
------------------------------------
FTP           24 Apr 2017         7
HTTP          24 Apr 2017         9
FTP           25 Apr 2017         6
HTTP          25 Apr 2017         2
------MISSING 26 Apr--------
FTP           27 Apr 2017         56
HTTP          27 Apr 2017         12
FTP           28 Apr 2017         5
------MISSING 29 Apr--------
HTTP          28 Apr 2017         17
FTP           30 Apr 2017         156
HTTP          30 Apr 2017         19

I would like to show rows WITH THE MISSING DATE even if there was not an incident saved on this day...

So the new OUTPUT should look like this.

WANTED OUTPUT :

[Channel]       [Time]          [Count]
------------------------------------
FTP             24 Apr 2017        7
HTTP            24 Apr 2017        9
FTP             25 Apr 2017        6
HTTP            25 Apr 2017        2
0               26 Apr 2017        0  -- here we go
FTP             27 Apr 2017        56
HTTP            27 Apr 2017        12
FTP             28 Apr 2017        5
HTTP            28 Apr 2017        17
0               29 Apr 2017        0  -- here we go
FTP             30 Apr 2017        156
HTTP            30 Apr 2017        19

I know there are answered question like mine and I was trying to remake my code but I failed.

( SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns )

( How to generate all dates between two dates )

Answer 1

Similar to @DhruvJoshi's answer but using a recursive CTE to generate the dates instead:

DECLARE @MinDate DATE = '20170424',
        @MaxDate DATE = '20170430';

WITH allDates AS
(
    SELECT @MinDate AS dates

    UNION ALL

    SELECT DATEADD(DAY, 1, ad.[dates] )
    FROM allDates AS ad
    WHERE ad.[dates] < @MaxDate
)

SELECT 
        ISNULL([MessageType].[Name],0) AS [Channel],
        dates AS [Time],
        COUNT([MessageType].[Name]) AS [Count]
FROM 
(
   SELECT dates
   FROM allDates
) AS T
LEFT JOIN 
@table1 ON T.dates=CONVERT(VARCHAR(11), @table1.[OccuredAtUtc], 106)
        LEFT JOIN @table2 ON ... = ...
GROUP BY dates,
         [MessageType].[Name]
ORDER BY [Time] ASC

Answer 2

You can use something like a Tally table to generate all dates between certain time interval.

SELECT 
        ISNULL([MessageType].[Name],0) AS [Channel],
        dates AS [Time],
        COUNT([MessageType].[Name]) AS [Count]
FROM 
(
   SELECT 
      TOP (DATEDIFF(d,'2017-04-24','2017-04-30')+1) 
       DATEADD(d,ROW_NUMBER() OVER( ORDER BY (SELECT 1))-1,'2017-04-24') dates
    FROM sys.objects a CROSS JOIN sys.objects b 
)T
LEFT JOIN 
@table1 ON T.dates=CONVERT(VARCHAR(11), @table1.[OccuredAtUtc], 106)
        LEFT JOIN @table2 ON ... = ...
AND ( [OccuredAtUtc] > '2017-04-24'
        AND [OccuredAtUtc] < '2017-04-30' )
GROUP BY dates,
         [MessageType].[Name]
ORDER BY [Time] ASC

For more explanation on Tally tables please read this article

Answer 3

declare @t table ( i int identity , b bit, d as dateadd (dd, i - 1, 0 ))

insert into @t (b)
VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0)

insert into @t (b)
select 0
from @t t1
cross apply ( select b from @t) as t2
cross apply ( select b from @t) as t3
cross apply ( select b from @t) as t4
cross apply ( select b from @t) as t5

select t.d, isnull(y.channel,0), count(y.[date])
from @t t
    left join yourtable y on y.[date] = t.d
where d between getdate() - 30 and getdate()
group by t.d, isnull(y.channel,0)