PopSQL
Get started
->

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
Shared queries and folders ✅ Version history ✅ One-click connection to SQL Server ✅
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