Prev: Setting of @@FETCH_STATUS
Next: SQL Server Profiler Determine Number Times User Account Used
From: Jeff Moden Jeff on 1 Dec 2009 10:57
Not to worry, Brad... the width of the output column doesn't come close to
the spec. If "cheaters" jump on the code, the output will be wrong.

--Jeff Moden

"Brad Schulz" wrote:

> 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: Plamen Ratchev on 1 Dec 2009 10:57
Sorry Brad, I did not realize you have some public contest with evaluating solutions. But I left out the fun part with
formatting strings which should still make for some good typing... :)

--
Plamen Ratchev
http://www.SQLStudio.com
From: Bob Barrows on 1 Dec 2009 11:00
Plamen Ratchev wrote:
> Sorry Brad, I did not realize you have some public contest with
> evaluating solutions. But I left out the fun part with formatting
> strings which should still make for some good typing... :)
>
plus preventing errors with different month values ...
--
HTH,
Bob Barrows


From: Plamen Ratchev on 1 Dec 2009 11:29
DATEADD(MONTH, 1, CAST(CAST(yr * 10000 + mth * 100 + 1 AS CHAR(8)) AS DATETIME)) AS end_date

I will leave the rest AS IS to keep the intrigue. My intention was not at all to attempt formatting as requested because
in my opinion it does not make sense to do this in T-SQL. It was just to demonstrate to the OP it is very doable.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Justin Blanding Justin on 1 Dec 2009 12:31


"Plamen Ratchev" wrote:

> 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
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Setting of @@FETCH_STATUS
Next: SQL Server Profiler Determine Number Times User Account Used