Prev: Setting of @@FETCH_STATUS
Next: SQL Server Profiler Determine Number Times User Account Used
From: Jay on 30 Nov 2009 21:05
http://beyondrelational.com/blogs/tc/archive/2009/11/30/tsql-challenge-18-generate-text-formatted-month-calendars.aspx

I didn't think this was possible:

TSQL Challenge 18 - Generate text formatted month calendars

declare @t table (Mth int, Yr int)
insert @t(Mth, Yr) select 8, 2009
insert @t(Mth, Yr) select 2, 1900
insert @t(Mth, Yr) select 10,1959

SELECT * FROM @t

Your job is to take the above table and generate calendars for the months and years given in the table. A calendar should be generated for each row in the table, using a single query (and no temp tables or table variables)

Expected Output


01.+-----------------------------+
02.| FEBRUARY 1900 |
03.|=============================|
04.| Sun Mon Tue Wed Thu Fri Sat |
05.|-----------------------------|
06.| 1 2 3 |
07.| 4 5 6 7 8 9 10 |
08.| 11 12 13 14 15 16 17 |
09.| 18 19 20 21 22 23 24 |
10.| 25 26 27 28 |
11.+-----------------------------+
12.+-----------------------------+
13.| OCTOBER 1959 |
14.|=============================|
15.| Sun Mon Tue Wed Thu Fri Sat |
16.|-----------------------------|
17.| 1 2 3 |
18.| 4 5 6 7 8 9 10 |
19.| 11 12 13 14 15 16 17 |
20.| 18 19 20 21 22 23 24 |
21.| 25 26 27 28 29 30 31 |
22.+-----------------------------+
23.+-----------------------------+
24.| AUGUST 2009 |
25.|=============================|
26.| Sun Mon Tue Wed Thu Fri Sat |
27.|-----------------------------|
28.| 1 |
29.| 2 3 4 5 6 7 8 |
30.| 9 10 11 12 13 14 15 |
31.| 16 17 18 19 20 21 22 |
32.| 23 24 25 26 27 28 29 |
33.| 30 31 |
34.+-----------------------------+


Rules

This challenge demonstrates skill in using Date Functions, Grouping, Pivoting, Numbers Table, Recursion and CTEs.
The resulting output is a single 31-character column called Calendar
The Month should be uppercase and should be rendered in the language that is set at runtime
The Month and Year are centered
The Day-Of-The-Week names are the first 3 letters of the days of the week, rendered in the language that is set at runtime. Sunday must be the first column
The calendars must be sorted in order
The output must be unchanged regardless of the SET DATEFIRST setting
From: Plamen Ratchev on 1 Dec 2009 00:15
Jay wrote:
> http://beyondrelational.com/blogs/tc/archive/2009/11/30/tsql-challenge-18-generate-text-formatted-month-calendars.aspx
>
> I didn't think this was possible:
>

This is easy to do, just useless exercise... Here is the solution, just add the extra formating for fun (run with
Results to Text to see the correct output):

;WITH
N0 AS (SELECT 1 AS n UNION ALL SELECT 1),
N1 AS (SELECT 1 AS n FROM N0 AS A, N0 AS B),
N2 AS (SELECT 1 AS n FROM N1 AS A, N1 AS B),
N3 AS (SELECT 1 AS n FROM N2 AS A, N2 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY n) AS n FROM N3),
Dates AS (
SELECT CAST(CAST(yr * 10000 + mth * 100 + 1 AS CHAR(8)) AS DATETIME) AS start_date,
CAST(CAST(yr * 10000 + (mth + 1) * 100 + 1 AS CHAR(8)) AS DATETIME) AS end_date
FROM @t),
Calendar AS (
SELECT DATEADD(DAY, n - 1, start_date) AS dt,
DATEDIFF(DAY, '18991231', DATEADD(DAY, n - 1, start_date)) / 7 AS grp
FROM Nums
JOIN Dates AS D
ON DATEADD(DAY, n - 1, start_date) >= start_date
AND DATEADD(DAY, n - 1, start_date) < end_date)
SELECT MAX(CASE WHEN LEFT(week_days, 2) = ' 1'
THEN CHAR(10) + CHAR(13) +
DATENAME(MONTH, dt) + ' ' + CAST(YEAR(dt) AS CHAR(4)) +
CHAR(10) + CHAR(13) +
RIGHT(SPACE(28) + week_days, 28)
ELSE week_days
END) AS week_days
FROM Calendar AS A
CROSS APPLY (SELECT RIGHT(' ' + CAST(DAY(dt) AS VARCHAR(2)), 2) + ' '
FROM Calendar AS B
WHERE B.grp = A.grp
ORDER BY dt
FOR XML PATH('')) AS L(week_days)
GROUP BY grp
ORDER BY grp;

/*

February 1900

1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28


October 1959

1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31


August 2009

1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31

*/


--
Plamen Ratchev
http://www.SQLStudio.com
From: Plamen Ratchev on 1 Dec 2009 00:35
And you can do very similar with pivoting... :)

;WITH
N0 AS (SELECT 1 AS n UNION ALL SELECT 1),
N1 AS (SELECT 1 AS n FROM N0 AS A, N0 AS B),
N2 AS (SELECT 1 AS n FROM N1 AS A, N1 AS B),
N3 AS (SELECT 1 AS n FROM N2 AS A, N2 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY n) AS n FROM N3),
Dates AS (
SELECT CAST(CAST(yr * 10000 + mth * 100 + 1 AS CHAR(8)) AS DATETIME) AS start_date,
CAST(CAST(yr * 10000 + (mth + 1) * 100 + 1 AS CHAR(8)) AS DATETIME) AS end_date
FROM @t),
Calendar AS (
SELECT DATEPART(WEEKDAY, DATEADD(DAY, n - 1, start_date)) AS wk_day,
DATEDIFF(DAY, '18991231', DATEADD(DAY, n - 1, start_date)) / 7 AS grp,
DAY(DATEADD(DAY, n - 1, start_date)) AS dt_day
FROM Nums
JOIN Dates AS D
ON DATEADD(DAY, n - 1, start_date) >= start_date
AND DATEADD(DAY, n - 1, start_date) < end_date)
SELECT RIGHT(' ' + COALESCE(LTRIM([1]), ' '), 2) + ' ' +
RIGHT(' ' + COALESCE(LTRIM([2]), ' '), 2) + ' ' +
RIGHT(' ' + COALESCE(LTRIM([3]), ' '), 2) + ' ' +
RIGHT(' ' + COALESCE(LTRIM([4]), ' '), 2) + ' ' +
RIGHT(' ' + COALESCE(LTRIM([5]), ' '), 2) + ' ' +
RIGHT(' ' + COALESCE(LTRIM([6]), ' '), 2) + ' ' +
RIGHT(' ' + COALESCE(LTRIM([7]), ' '), 2)
FROM Calendar AS C
PIVOT
(MAX(dt_day) FOR wk_day IN ([1], [2], [3], [4], [5], [6], [7])) AS P;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Brad Schulz Brad on 1 Dec 2009 10:15
Ahhh, Plamen, I wish you hadn't posted all that code.

This is a TSQL Challenge... a contest that just opened yesterday:

http://beyondrelational.com/blogs/tc/archive/2009/11/30/tsql-challenge-18-generate-text-formatted-month-calendars.aspx

I guess we'll be taking a closer look now at the solutions that are
submitted to try to make sure they didn't steal your code too much.

--Brad


From: Jay on 1 Dec 2009 10:43
I was hoping for discussion, not answers. After all, I did my best to make
it clear it was a contest.

Still, I don't really understand that SQL and it doesn't seem to completely
satisfy the requirements.

"Brad Schulz" <Brad Schulz(a)discussions.microsoft.com> wrote in message
news:C0D307DD-866D-4CFD-9695-3C5D55348CA0(a)microsoft.com...
> Ahhh, Plamen, I wish you hadn't posted all that code.
>
> This is a TSQL Challenge... a contest that just opened yesterday:
>
> http://beyondrelational.com/blogs/tc/archive/2009/11/30/tsql-challenge-18-generate-text-formatted-month-calendars.aspx
>
> I guess we'll be taking a closer look now at the solutions that are
> submitted to try to make sure they didn't steal your code too much.
>
> --Brad
>
>


 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Setting of @@FETCH_STATUS
Next: SQL Server Profiler Determine Number Times User Account Used