Post date: Jun 06, 2014 12:55:46 AM
"How many Sundays fell on the first of the month from 1/1/1901 to 12/31/2000?"
This is a problem that is very easy to solve in SQL.
; WITH Ten (i) AS
(
SELECT 0
UNION ALL
SELECT i + 1
FROM Ten
WHERE i < 9
),
Integers (i) AS
(
SELECT 10000 * k10.i + 1000 * k.i + 100 * h.i + 10 * t.i + o.i
FROM Ten o
CROSS JOIN Ten t
CROSS JOIN Ten h
CROSS JOIN Ten k
CROSS JOIN Ten k10
),
Dates (d) AS
(
SELECT DATEADD(day, i, '1/1/1901')
FROM Integers
)
SELECT COUNT(*)
FROM Dates
WHERE d >= '1/1/1901'
AND d < '1/1/2001'
AND DATEPART(weekday, d) = 1
AND DATEPART(day, d) = 1
Except that Project Euler wants you to follow your basic rules about the length of the months, not cheat. So let's do this the right way. Now I don't want to replicate any of the thousands of pages that already talk about building a date table in SQL (seriously, this is a boring problem), so we'll still keep things basic.
CREATE TABLE #Dates (id int PRIMARY KEY NOT NULL, MonthName varchar(50) NULL)
; WITH Ten (i) AS
(
SELECT 0
UNION ALL
SELECT i + 1
FROM Ten
WHERE i < 9
)
INSERT INTO #Dates (id)
SELECT 10000 * k10.i + 1000 * k.i + 100 * h.i + 10 * t.i + o.i + 1
FROM Ten o
CROSS JOIN Ten t
CROSS JOIN Ten h
CROSS JOIN Ten k
CROSS JOIN Ten k10
WHERE 10000 * k10.i + 1000 * k.i + 100 * h.i + 10 * t.i + o.i + 1 < 37000
DECLARE @y int = 1901, @f int
-- Do each year from 1901 to 2000
WHILE 1 = 1
BEGIN
-- If I could make a subroutine in T-SQL, I would have done it. I'm not going to make a 2 line SP.
-- But since I can't, copy/paste is faster than a bunch of nested ifs.
; WITH sub AS (SELECT TOP 31 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Jan ' + RTRIM(@y)
-- February and leap years
-- This handles the case where leap years don't occur on centuries unless divisible by 400. But I could have skipped it because 2000 is divisible by 400.
IF (@y % 4 = 0 AND @y % 100 != 0) OR (@y % 4 = 0 AND @y % 100 = 0 AND @y % 400 = 0)
SET @f = 29
ELSE
SET @f = 28
; WITH sub AS (SELECT TOP (@f) * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Feb ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 31 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Mar ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 30 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Apr ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 31 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'May ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 30 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Jun ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 31 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Jul ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 31 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Aug ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 30 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Sep ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 31 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Oct ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 30 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Nov ' + RTRIM(@y)
; WITH sub AS (SELECT TOP 31 * FROM #Dates WHERE MonthName IS NULL ORDER BY id)
UPDATE sub SET MonthName = 'Dec ' + RTRIM(@y)
SET @y = @y + 1
IF @y = 2001 BREAK
END -- Loop though each year
; WITH Dates AS
(
SELECT (ROW_NUMBER() OVER (ORDER BY id) + 365) % 7 AS DayOfWeek
, ROW_NUMBER() OVER (PARTITION BY MonthName ORDER BY id) AS DayOfMonth
FROM #Dates
)
SELECT COUNT(*)
FROM Dates
WHERE DayOfMonth = 1
AND DayOfWeek = 0
DROP TABLE #Dates