How to Avoid Gaps in Data in SQL Server 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.
