SQL - SHOW ALL DATES between two dates
I have the following data stored as dates in mine [OccuredAtUtc]
which looks 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 correctly returns data from two intervals (from the 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 doesn't display the line "April 26, 2017" and "April 29, 2017" because there are no records in my database these days.
OLD OUTPUT: absent on April 26 and 29.
[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 be able to display the WITH THE MISSING DATE lines even if there was no incident on that 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 is an answer to a question like mine and I tried to rewrite my code but I failed.
source to share
Similar to @DhruvJoshi, but using a recursive CTE to generate dates instead of:
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
source to share
You can use something like a Tally table to generate all dates between a specific 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
Read more about Tally tables in this article.
source to share
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)
source to share