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 join uses the convert() function to make sure both times are formatted identically. 105 denotes '19-02-2020' format, but here's a full list of format codes.

How to Use Coalesce

How to Import a CSV

From SQL Server query to chart to Slack in seconds

Get to answers faster, together, with PopSQL and SQL Server