Introducing PopSQL + dbt. The first SQL editor with built-in dbt support.Read more →
Sign In

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 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.

Previous

How to Get First Row Per Group in SQL Server

Next

How to Do Type Casting in SQL Server

Ready for a modern SQL editor?

Get started for free