XML FeedsWe 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 2013-01-04 0 2013-01-05 2 2013-01-06 0 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
- ;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
- 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
- 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.....
- 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
- -- 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
- -- 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
- 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
- INSERT Calendar values('20240101')
Select what we just inserted
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
The way I would solve this problem is differently. I would insert explicitly in the table rows with 0 values for those dates that don't exist. For example:
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.