Sign In

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.

Cta
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and SQL Server
Get started in minutes
Previous
How to Get First Row Per Group
Next
How to Do Type Casting