We got a question today from a user who wanted to display counts of zero where dates were missing. For example if you had the following data

OrderDate	SomeCount
2013-01-01	2
2013-01-02	2
2013-01-03	1
2013-01-05	2
2013-01-07	1
2013-01-08	1
2013-01-09	2

What you really wanted is the following

OrderDate	SomeCount
2013-01-01	2
2013-01-02	2
2013-01-03	1
<strong>2013-01-04	0</strong>
2013-01-05	2
<strong>2013-01-06	0</strong>
2013-01-07	1
2013-01-08	1
2013-01-09	2

As you can see we added January 4th and January 6th to the results with a count of zero.

Let’s get started, first we are going to created a calendar table

Here is a simple way to return a bunch of dates

T-SQL
1
2
3
4
5
6
7
;with cte as (select row_number() over(order by s1.name) as Row
from sysobjects s1
cross join sysobjects s2)
 
 
select  dateadd(day,Row,'20091231') from cte
where Row < 5114
;with cte as (select row_number() over(order by s1.name) as Row
from sysobjects s1
cross join sysobjects s2)


select  dateadd(day,Row,'20091231') from cte
where Row < 5114

That will return dates between 2010-01-01 and 2023-12-31

Now let’s create the calendar table and insert the rows from the query above

T-SQL
1
2
3
4
5
6
7
8
9
10
CREATE TABLE Calendar (SomeDate date not null primary key)
GO
 
;with cte as (select row_number() over(order by s1.name) as Row
from sysobjects s1
cross join sysobjects s2)
 
INSERT Calendar
select  dateadd(day,Row,'20091231') from cte
where Row < 5114
CREATE TABLE Calendar (SomeDate date not null primary key)
GO

;with cte as (select row_number() over(order by s1.name) as Row
from sysobjects s1
cross join sysobjects s2)

INSERT Calendar
select  dateadd(day,Row,'20091231') from cte
where Row < 5114

You might want to adjust the range to start earlier or end later for your purpose

Now that the calendar table is ready, let’s create a fake order table with some dates

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
create table SomeData (OrderDate date, SomeCol int)
insert SomeData values('2013-01-01',1)
insert SomeData values('2013-01-01',1)
insert SomeData values('2013-01-02',1)
insert SomeData values('2013-01-02',1)
insert SomeData values('2013-01-03',1)
insert SomeData values('2013-01-05',1)
insert SomeData values('2013-01-05',1)
insert SomeData values('2013-01-07',1)
insert SomeData values('2013-01-08',1)
insert SomeData values('2013-01-09',1)
insert SomeData values('2013-01-09',1)
create table SomeData (OrderDate date, SomeCol int)
insert SomeData values('2013-01-01',1)
insert SomeData values('2013-01-01',1)
insert SomeData values('2013-01-02',1)
insert SomeData values('2013-01-02',1)
insert SomeData values('2013-01-03',1)
insert SomeData values('2013-01-05',1)
insert SomeData values('2013-01-05',1)
insert SomeData values('2013-01-07',1)
insert SomeData values('2013-01-08',1)
insert SomeData values('2013-01-09',1)
insert SomeData values('2013-01-09',1)

Querying from this table…..

T-SQL
1
2
3
select OrderDate, count(somecol) as SomeCount
from SomeData
group by OrderDate
select OrderDate, count(somecol) as SomeCount
from SomeData
group by OrderDate

Here are the results

OrderDate	SomeCount
2013-01-01	2
2013-01-02	2
2013-01-03	1
2013-01-05	2
2013-01-07	1
2013-01-08	1
2013-01-09	2

As you can see January 4th and January 6th are missing. Let’s do a left join with the calendar table to display the results

Here is the code, I will go over it a little later

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- grab min and max dates or supply range yourself
declare @mindate date,@maxdate date
select @mindate =min(OrderDate),@maxdate = max(OrderDate)
from SomeData
 
--here is the query
select c.SomeDate,coalesce(x.SOmeCount,0) as TheCount
from Calendar c
left join (
select OrderDate, count(somecol) as SOmeCount
from SomeData
group by OrderDate) x
on c.SomeDate = x.OrderDate
where c.SomeDate between @mindate  and @maxdate
order by c.SomeDate
-- grab min and max dates or supply range yourself
declare @mindate date,@maxdate date
select @mindate =min(OrderDate),@maxdate = max(OrderDate)
from SomeData
 
--here is the query
select c.SomeDate,coalesce(x.SOmeCount,0) as TheCount
from Calendar c
left join (
select OrderDate, count(somecol) as SOmeCount
from SomeData
group by OrderDate) x
on c.SomeDate = x.OrderDate
where c.SomeDate between @mindate  and @maxdate
order by c.SomeDate

Running that will give you this

SomeDate	TheCount
2013-01-01	2
2013-01-02	2
2013-01-03	1
2013-01-04	0
2013-01-05	2
2013-01-06	0
2013-01-07	1
2013-01-08	1
2013-01-09	2

So what are we doing in the code? Here it is again

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- grab min and max dates or supply range yourself
declare @mindate date,@maxdate date
select @mindate =min(OrderDate),@maxdate = max(OrderDate)
from SomeData
 
--here is the query
select c.SomeDate,coalesce(x.SOmeCount,0) as TheCount
from Calendar c
left join (
select OrderDate, count(somecol) as SOmeCount
from SomeData
group by OrderDate) x
on c.SomeDate = x.OrderDate
where c.SomeDate between @mindate  and @maxdate
order by c.SomeDate
-- grab min and max dates or supply range yourself
declare @mindate date,@maxdate date
select @mindate =min(OrderDate),@maxdate = max(OrderDate)
from SomeData
 
--here is the query
select c.SomeDate,coalesce(x.SOmeCount,0) as TheCount
from Calendar c
left join (
select OrderDate, count(somecol) as SOmeCount
from SomeData
group by OrderDate) x
on c.SomeDate = x.OrderDate
where c.SomeDate between @mindate  and @maxdate
order by c.SomeDate

On line 2,3 and 4 we are grabbing the min and max dates and storing those in variables which we will use in the WHERE clause on line 14
On line 7 we grab the date from the calendar table and also use the coalesce function to display 0 for the non existing rows which will come back as NULLS
Line 9 is the LEFT JOIN
Line 10 until 12 is the subquery where we are grouping the rows based on date, you also see x on line 12, that is the alias for the sub query
Line 13 has the join condition between the calendar table aliased c and the subquery aliased x
Line 14 is where we use the min and max date from lines 2,3 and 4
Line 15 does the sorting

Pretty simple right?

Your calendar table does not have to be that simple, you can add IsWeekday, IsWeekend, IsHoliday and other flags, you can also add the names of the dates

You can also have these be computed columns, for example here we are adding a computed column that will have the week number

T-SQL
1
ALTER TABLE calendar ADD WeekNum AS DATEPART(wk,SomeDate)
ALTER TABLE calendar ADD WeekNum AS DATEPART(wk,SomeDate)

Now you can see that the column is showing up

SELECT * FROM Calendar
SomeDate	WeekNum
2010-01-01	1
2010-01-02	1
2010-01-03	2
2010-01-04	2
2010-01-05	2
.....           ...

And if you add rows, you don’t have to update the column

T-SQL
1
INSERT Calendar values('20240101')
INSERT Calendar values('20240101')

Select what we just inserted

Text
1
2
SELECT * FROM Calendar
where SomeDate = '20240101'
SELECT * FROM Calendar
where SomeDate = '20240101'

And here is the result

SomeDate	WeekNum
2024-01-01	1

That is it for this post, hopefully it will help out somebody and hopefully someone will be adding a calendar table today