Is it a plane? Is it a ... No it's SQLMan!! Forged in the laboratory of Dr. Codd, he got the powers of SQL:2003, ISO-11179 and ISO-8601. He is here to rescue us from Web 2.0 XP-Agile-cowboy coders, who have just discovered the incredible powers of the relational database. But in theire feeble quest to take over the world, they misuse their new found powers using surrogate keys, map tables to classes, don't abstract from the underlaying architecture, thinking colums are fields and reading "Teach yourself SQL in 24 hours" rader than "SQL programming Style". The powers of SQL takes decenniums to master, in the wrong unexperienced hands it may be a totally disaster. Luckily SQLman is here now!
Read about he's ongoing fight against the unlighted here!
This post was inspired by this post by Ken Henderson ...
Ruby on Rails, the convention over configuration framework, wants us to have an ID column named ID as a primary key in every table. Now THAT is poor SQL! If we look at the Microsoft Adventureworks database they use singular form object for table names and use the singular form+ID for every table as in Vendor and VendorID. Poor SQL! So what should you use?
I use my own convention.:
Say I have a table Vendors, then the Vendor ID column I call Vendor. For foreign keys to Vendors I call the column Vendor, rather than as Rails would like me to Vendor_ID.
For me this works very well. I find it very clear and consistent. When I talk to customers they always refer to stuff like "the Project" meaning the Project ID for a given Project. So in my Projects table I have the primary key column Project. Rails is very good at distinguishing plural forms and singular forms of words so why didn't they come up with this convention?
And why do Microsoft make a sample database full of horrible SQL? Beats me! Or I may have read to much Joe Celko.
"Joe Celko the SQL apprentice" blog can be found here. I'm pretty sure it's not maintained by Mr. Celko, but the answers are very Celkoish (Would Celko ever reference SQL Server 2005 as SQL-2005?? NO!!!)
Now I have to hack Rails to follow my convention ;-)
Here is the SQL server code, just cut and paste into a querywindow in Managment Studio and hit F5
DECLARE @Delimitedtext varchar(max);
DECLARE @Delimiter char(1);
SET @Delimitedtext = 'aaa,bbbb,cccc,dddd,';
SET @Delimiter = ',';
WITH Strings(s, r)
SUBSTRING(@Delimitedtext,1, CHARINDEX(@Delimiter, @Delimitedtext)-1) s,
SUBSTRING(@Delimitedtext,CHARINDEX(@Delimiter, @Delimitedtext)+1, len(@Delimitedtext)) r
SUBSTRING(r,1, CHARINDEX(@Delimiter, r)-1) s,
SUBSTRING(r,CHARINDEX(@Delimiter, r)+1, len(r)) r
CHARINDEX(@Delimiter, r) > 0
SELECT s FROM Strings
The query will yield this result:
(4 row(s) affected)
Have fun Querying (SQL is not defined as a programming language, so you really can't have fun programming SQL ;-))
But as we are at it we might as well split the date into year, month, day, quarter, add a holiday flag, a workday flag and a description column to indicate special days.
The calendar table is of course also useful in outer joins where you want to retrieve a row for every date even if there are no data for that date.
With the advent of CTE's creating a table just for the join is no longer necessary as you can use a recursive CTE for it, much as shown in my earlier post.
Continuing on the standard auixiliary schema:
Here comes the commented script for creating and populating the table:
CREATE TABLE [Auxiliary].[Calendar] (
-- This is the calendar table
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Quarter] int NOT NULL,
[Month] int NOT NULL,
[Week] int NOT NULL,
[Day] int NOT NULL,
[DayOfYear] int NOT NULL,
[Weekday] int NOT NULL,
[Fiscal_Year] int NOT NULL,
[Fiscal_Quarter] int NOT NULL,
[Fiscal_Month] int NOT NULL,
[KindOfDay] varchar(10) NOT NULL,
[Description] varchar(50) NULL,
PRIMARY KEY CLUSTERED ([Date])
ALTER TABLE [Auxiliary].[Calendar]
-- In Celkoish style I'm manic about constraints (Never use em ;-))
ADD CONSTRAINT [Calendar_ck] CHECK ( ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week] BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601
-- thus first day of a week is monday
-- A recursive CTE that produce all dates between 1999 and 2020-12-31
SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
SELECT (Date + 1) AS Date
Date < cast('2021' AS DateTime) -1
-- The weeks can be found by counting the thursdays in a year so we find
-- the thursday in the week for a particular date
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
-- Now we produce the weeknumers for the thursdays
-- ROW_NUMBER is new to SQL Server 2005
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
WHERE DATEPART(weekday,Date) = 4
INSERT INTO Auxiliary.Calendar
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,
-- Fiscal year may be different to the actual year, in Norway the are the same
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,
-- Holidays in Norway
-- For other countries and states: Wikipedia - List of holidays by country
-- I wrote about the computus function here
WHEN (DATEPART(DayOfYear, d.Date) = 1) -- New Year's Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))-7) -- Palm Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-3) -- Maundy Thursday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-2) -- Good Friday
OR (d.Date = Auxiliary.Computus(YEAR(Date))) -- Easter Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- Ascension Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- Pentecost
OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- Whitmonday
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) -- Labour day
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) -- Constitution day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) -- Cristmas day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) -- Boxing day
WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
-- Description of holidays in Norway
WHEN (DATEPART(DayOfYear, d.Date) = 1) THEN 'New Year''s Day'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7) THEN 'Palm Sunday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3) THEN 'Maundy Thursday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2) THEN 'Good Friday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))) THEN 'Easter Sunday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN 'Ascension Day'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN 'Pentecost'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN 'Whitmonday'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) THEN 'Labour day'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) THEN 'Constitution day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) THEN 'Cristmas day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) THEN 'Boxing day'
FROM DatesAndThursdayInWeek d
-- This join is for getting the week into the result set
inner join Weeks w
on d.Thursday = w.Thursday
You can download the entire script here.
While I write som nice examples you can find additional resources and examples on using calendar tables here:
Why should I consider using an auxiliary calendar table? by Aaron Bertrand
Auxiliary Angst by Joe Celko
Getting previous month/years/days from your calendar table
by Louis Davidson