# Friday the 13th

• Jan Van der Eecken

SSCrazy Eights

Points: 9004

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

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

• ibraheem.alkilanny

Valued Member

Points: 62

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

• Luis Cazares

SSC Guru

Points: 183645

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

SSC Guru

Points: 183645

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
• tomaz.kastrun

SSCrazy

Points: 2085

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 | blog:  https://tomaztsql.wordpress.com/

• Jan Van der Eecken

SSCrazy Eights

Points: 9004

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)

• Jan Van der Eecken

SSCrazy Eights

Points: 9004

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)

• sgriffin-681047

Valued Member

Points: 64

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';

• Jeff Moden

SSC Guru

Points: 997328

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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"