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 |