# Friday the 13th

• Comments posted to this topic are about the item Friday the 13th

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

• The value of

`n = ROW_NUMBER() OVER (ORDER BY (SELECT null))`

can be simplified to
n = a.n * 10 + b.n

Which is faster, cleaner and less overhead on server

• ibraheem.alkilanny - Monday, May 7, 2018 12:02 AM

The value of

`n = ROW_NUMBER() OVER (ORDER BY (SELECT null))`

can be simplified to
n = a.n * 10 + b.n

Which is faster, cleaner and less overhead on server

` SELECT n = a.n + (b.n *10) FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) -- 10 rows CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) b(n) `

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Luis Cazares - Monday, May 7, 2018 8:28 AM

ibraheem.alkilanny - Monday, May 7, 2018 12:02 AM

The value of

`n = ROW_NUMBER() OVER (ORDER BY (SELECT null))`

can be simplified to
n = a.n * 10 + b.n

Which is faster, cleaner and less overhead on server

` SELECT n = a.n + (b.n *10) FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) -- 10 rows CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) b(n) `

It works on either way you want to phrase it.
` SELECT n = a.n * 12 + b.n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n) -- 10 rows CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) b(n) ORDER BY 1`

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Couple of shorter solutions:

`--Solution 1SET DATEFIRST 1DECLARE @d DATE = '2016-05-13 00:00:00.000'--GETDATE()WHILE (@d < '2099/01/01')  BEGIN   IF DATEPART(dw,DATEFROMPARTS(YEAR(@d), MONTH(@d), 13)) = 5    BEGIN      SELECT @d AS Friday13    END   SET @d = DATEADD(m,1,@d)  END`

`--Solution 2SELECT n+12 FROM     (SELECT DATEADD(M,ROW_NUMBER() OVER (ORDER BY id)-1,'20160101')n FROM sys.syscolumns)N WHERE DATEPART(dw, n)=7`

`--Solution 3DECLARE @d1 DATETIME=42501    WHILE @d1<=73049    BEGIN        SET @d1+=7    IF(DAY(@d1)=13)     SELECT @d1    END`

We had  a running gag  who can post Friday 13th T-SQL in a tweet (meaning max 140 chars). More on this blogpost: https://tomaztsql.wordpress.com/2016/05/13/friday-13th-with-t-sql-a-shortest-competition/

Best, Tomaž

Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog:  https://tomaztsql.wordpress.com/

• Luis Cazares - Monday, May 7, 2018 8:34 AM

Luis Cazares - Monday, May 7, 2018 8:28 AM

ibraheem.alkilanny - Monday, May 7, 2018 12:02 AM

The value of

`n = ROW_NUMBER() OVER (ORDER BY (SELECT null))`

can be simplified to
n = a.n * 10 + b.n

Which is faster, cleaner and less overhead on server

` SELECT n = a.n + (b.n *10) FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) -- 10 rows CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) b(n) `

It works on either way you want to phrase it.
` SELECT n = a.n * 12 + b.n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n) -- 10 rows CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) b(n) ORDER BY 1`

Thanks guys, I would never ever had thought of doing it that way. But yeah, one never stops learning.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

• tomaz.kastrun - Monday, May 7, 2018 11:53 AM

Couple of shorter solutions:

`--Solution 1SET DATEFIRST 1DECLARE @d DATE = '2016-05-13 00:00:00.000'--GETDATE()WHILE (@d < '2099/01/01')  BEGIN   IF DATEPART(dw,DATEFROMPARTS(YEAR(@d), MONTH(@d), 13)) = 5    BEGIN      SELECT @d AS Friday13    END   SET @d = DATEADD(m,1,@d)  END`

`--Solution 2SELECT n+12 FROM     (SELECT DATEADD(M,ROW_NUMBER() OVER (ORDER BY id)-1,'20160101')n FROM sys.syscolumns)N WHERE DATEPART(dw, n)=7`

`--Solution 3DECLARE @d1 DATETIME=42501    WHILE @d1<=73049    BEGIN        SET @d1+=7    IF(DAY(@d1)=13)     SELECT @d1    END`

We had  a running gag  who can post Friday 13th T-SQL in a tweet (meaning max 140 chars). More on this blogpost: https://tomaztsql.wordpress.com/2016/05/13/friday-13th-with-t-sql-a-shortest-competition/

Best, TomaÅ¾

All valid solutions, Tomaz. But the whole point was about how to do this without introducing RBAR ( Row By Agonizing Row) logic to it. Just a clean set based solution to it.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

• A fun challenge!  Here's a solution using a recursive cte:

DECLARE @FirstMonth DATE = GETDATE();
;WITH [cte] AS (
SELECT DATEFROMPARTS(DATEPART(YEAR,@FirstMonth), DATEPART(MONTH,@FirstMonth), 13) AS [d] -- the 13th for our first month
UNION ALL
SELECT DATEADD(MONTH,1,[d]) FROM [cte] -- a new row for every subsequent month
)
SELECT TOP(10) [d]
FROM [cte]
WHERE DATENAME(WEEKDAY,[d]) = 'Friday';

• sgriffin-681047 - Wednesday, May 9, 2018 12:00 PM

A fun challenge!  Here's a solution using a recursive cte:

DECLARE @FirstMonth DATE = GETDATE();
;WITH [cte] AS (
SELECT DATEFROMPARTS(DATEPART(YEAR,@FirstMonth), DATEPART(MONTH,@FirstMonth), 13) AS [d] -- the 13th for our first month
UNION ALL
SELECT DATEADD(MONTH,1,[d]) FROM [cte] -- a new row for every subsequent month
)
SELECT TOP(10) [d]
FROM [cte]
WHERE DATENAME(WEEKDAY,[d]) = 'Friday';

I agree with Jan Van der Eecken above about set based solutions.  Recursive CTEs (rCTE) qualify as RBAR when assigned the task of counting and can actually be beaten by a well formed WHILE loop in a transaction.  Of course, set based "Pseudo Cursors" (like many of those posted already) blow both out of the water.  And, no... I'm not prone to justifying the use of RBAR just because there's a low rowcount.  The following article demonstrates the nature of rCTEs that increment and how bad they actually are even for low row counts.

Hidden RBAR: Counting with Recursive CTE's

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.