Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
It’s Memorial Day in the US. A holiday, though I’m off on a trip to Germany today.
I wanted to have a fun Memorial Day Question of the Day today, and I decided to write some code to calculate Memorial Day. This post looks at how the code works.
The Algorithm
Memorial day is always the last Monday in May. For me, I decided to find all the Mondays in May and then take the last one. I started with a tally table to get a list of days. In any given year, we can find the first day of the year with this code:
DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0)
Now I use a DATEADD with my tally table to find the first 200 days of the year. The end of May will always fall in this number of days. Here is the code for a list of the first 200 days of the current year:
WITH myTally (n)
AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY
(SELECT NULL))
FROM
( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a (n)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b (n)
CROSS JOIN ( VALUES (1), (2)) c (n) )
, cteCurrYearDates (myDate)
AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
FROM myTally)
, cteMay (Mondays)
Once I have this, I now need to get the Mondays in May. I can use the DATEPART and MONTH functions to find this. Actually, I ought to use DATEPART to be consistent here, but my habit is MONTH() for the month.
I also need to set the DATEFIRST for this code, otherwise the day of the week will be inconsistent. Here’s the code for this:
SET DATEFIRST 7;
WITH myTally (n)
AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY
(SELECT NULL))
FROM
( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a (n)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b (n)
CROSS JOIN ( VALUES (1), (2)) c (n) )
, cteCurrYearDates (myDate)
AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
FROM myTally)
, cteMay (Mondays)
AS ( SELECT cteCurrYearDates.myDate
FROM cteCurrYearDates
WHERE
DATEPART (WEEKDAY, cteCurrYearDates.myDate) = 2
AND MONTH(cteCurrYearDates.myDate) = 5
)
This gives me a list of Mondays in May for the current year. I want the last one, which isn’t easy to do in a result set. However, I can get the first one with a TOP 1 limit. The easy way to get the last one is reverse the order of the rows and then take the first one. I do this with an ORDER BY.
Here’s the complete code:
SET DATEFIRST 7;
WITH myTally (n)
AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY
(SELECT NULL))
FROM
( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a (n)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b (n)
CROSS JOIN ( VALUES (1), (2)) c (n) )
, cteCurrYearDates (myDate)
AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
FROM myTally)
, cteMay (Mondays)
AS ( SELECT cteCurrYearDates.myDate
FROM cteCurrYearDates
WHERE
DATEPART (WEEKDAY, cteCurrYearDates.myDate) = 2
AND MONTH(cteCurrYearDates.myDate) = 5
)
SELECT --TOP 1
Mondays
FROM cteMay
ORDER BY cteMay.Mondays DESC;
Now I can get Memorial Day for the current year.
SQL New Blogger
This is a great example of breaking down an algorithm and explaining it to the reader. If you write T-SQL code for a living, you might write a series of posts on how you solve various problems in T-SQL and explain the process. Link to places where you learn, and show some results that give a feeling for how you built the code.
This took about 10 minutes to write once I’d built all the code. I didn’t go into details with individual result sets, but you could easily do that to show how the code works.