How to Avoid Gaps in Data in SQL Server
Let's say you're grouping by time in SQL Server and you don't want gaps in your report data.
First, generate a series of date/time values that have no gaps using a common table expression:
declare @start_datetime datetime = '2020-02-01';
declare @end_datetime datetime = '2020-02-29';
with cte_date (datetime) as (
select @start_datetime
union all
select dateadd(hour, 1, datetime) --or minute, day, week, month instead of hour
from cte_date
where datetime < @end_datetime
)
select datetime
from cte_date
option (maxrecursion 0);Note: highlight the entire statement before running in PopSQL as semi-colons usually denote multiple statements.
datetime
------------------------
2020-02-01T00:00:00.000Z
2020-02-01T01:00:00.000Z
2020-02-01T02:00:00.000Z
2020-02-01T03:00:00.000Z
...
Now you can left join your date/time series data against this gapless series. Here's how you could create a count of sessions for each day:
declare @start_datetime datetime = '2020-02-01';
declare @end_datetime datetime = '2020-02-29';
with cte_date (datetime) as (
select @start_datetime
union all
select dateadd(day, 1, datetime)
from cte_date
where date < @end_datetime
)
select
cte_date.datetime,
COUNT(s.id) as session_ct
from cte_date
left outer join sessions s on convert(varchar(10), s.created_at, 105) = convert(varchar(10), cte_date.datetime, 105)
group by datetime
option (maxrecursion 0);Note: the
left outer joinuses theconvert()function to make sure both times are formatted identically.105denotes '19-02-2020' format, but here's a full list of format codes.
