I came across a request to show data out of sql for every day in a month. I know that I can't assume that my data will have results for every day, so I have to show days where there was no activity. An easy way to achieve this is in SQL is with a Date table; sometimes they are also called Calendar tables.
Man am I ever glad I paid attention when someone told me about these things! It makes building queries against dates much faster.
Here is an example end result. (I included the build scripts below)
SELECT
dt.date,
o.order_date,
o.order_number
FROM
Date.DateTable dt
left join dbo.Orders o
on dt.date = o.order_date
WHERE
dt.date
between '9/1/2012'
and '9/5/2012'
;
This will show every day 9/1 - 9/5, and if there is an order that particular day, it will show those as well. Otherwise the query will show NULL for o.order_date and o.order_number. I did that just to show what was going on in the left join.
There are a ton of examples out there on how to build Date tables, and this is just my version. The idea is that you build this table once. Make sure you cover all the dates and date-parts you expect to be asked for. For example, sometimes have to deal with Julian dates, so I incorporated them into the date table to handle those instances.
This script is a copy of one I used a few years ago, and contains a few user-defined functions, schemas, etc. So I wouldn't just copy-paste if I were you!
The build script:
--BEGIN TRAN T1;
--drop table Date.DateTable
-- Create the table (I use a "DATE" schema for date-related objects)
-- Some of these fields are just things I know the client asks for a lot.
CREATE TABLE Date.DateTable
(
date_id INT
PRIMARY KEY,
[date] datetime,
[month] tinyint,
[day] tinyint,
[year] char(4),
[quarter] tinyint,
[week] tinyint,
[year_day] smallint,
[week_day] tinyint,
[is_first_day_in_month] tinyint,
[is_last_day_in_month] tinyint,
string_date varchar(10),
formatted_date varchar(30),
day_name varchar(10),
month_name varchar(12),
julian_date int,
date_endOfDay datetime,
firstDayOfMonth datetime,
lastDayOfMonth datetime
)
;
-- Setup the date range I want to insert into the table
DECLARE @Date DATETIME;
declare @end_date datetime;
SET @Date = '1/1/1970';
set @end_date = '12/31/2100';
-- Just a count of the number of rows I should expect
select datediff(dd, @Date, @end_date)
-- 47846
-- I use a while statement over other options (cursor) because
-- I feel they are fairly easy to read and you only have to run this script once anyway.
WHILE @Date <= @end_date
BEGIN
-- My insert statement uses a few user-defined functions,
-- but you can figure out what they do fairly easily.
INSERT INTO Date.DateTable (
date_id,
[date],
[month],
[day],
[year],
[quarter],
[week],
[year_day],
[week_day],
[is_first_day_in_month],
[is_last_day_in_month],
string_date,
formatted_date,
day_name,
month_name,
julian_date,
date_endOfDay,
firstDayOfMonth,
lastDayOfMonth
)
select
CONVERT(VARCHAR(8), @Date, 112)
as [date_id],
@Date
as [date],
DATEPART(mm, @Date)
as [month],
DATEPART(dd, @Date)
as [day],
year(@Date)
as [year],
DATEPART(qq, @Date)
as [Quarter],
DATEPART(ww, @Date)
as [week],
DATEPART(dy, @Date)
as [year_day],
DATEPART(dw, @Date)
as [week_day],
case when (datediff(dd, @Date, DATE.FirstDayOfMonth(@Date)) = 0)
then 1
else 0
end as [is_first_day_in_month],
case when (datediff(dd, @Date, DATE.LastDayOfMonth(@Date)) = 0)
then 1
else 0
end as [is_last_day_in_month],
CONVERT(VARCHAR(10), @date, 101)
as [string_date],
DATENAME(MM, @date) +
RIGHT(
CONVERT(VARCHAR(12), @date, 107), 9)
as [formatted_date],
DATENAME(dw, @Date)
as [day_name],
DATENAME(mm, @Date)
as [month_name],
DATE.Convert_to_Julian(@Date)
as [julian_date],
-- an int representation of a date, used by client
DATE.EndOfDay(@Date)
as [date_endOfDay],
-- the Last possible (milli) second of the day
DATE.FirstDayOfMonth(@Date)
as [firstDayOfMonth],
DATE.EndOfDay(DATE.LastDayOfMonth(@Date))
as [lastDayOfMonth]
;
SET @Date = DATEADD(dd, 1, @Date);
END
-- Hopefully this will equal 47846
select count(*)
from Date.DateTable
--ROLLBACK TRAN T1;
Just in case you're interested, here is the build script for the example (i.e. the Orders table):
CREATE TABLE [dbo].[Orders](
[order_id] [int]
IDENTITY(1,1)
NOT NULL,
[order_date] [datetime]
NOT NULL,
[order_number] [nvarchar](50)
NOT NULL
)
ON [
PRIMARY]
;
insert into dbo.Orders (order_date, order_number)
select '9/1/2012', '
Order 1'
union all
select '9/5/2012', '
Order 2'
;
