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)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • 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

    Actually, it should be like this and start with zero.

    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

    Actually, it should be like this and start with zero.

    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 1
    SET DATEFIRST 1
    DECLARE @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 2
    SELECT 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 3
    DECLARE @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

    Actually, it should be like this and start with zero.

    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)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Jan Van der Eecken

    SSCrazy Eights

    Points: 9004

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

    Couple of shorter solutions:

    --Solution 1
    SET DATEFIRST 1
    DECLARE @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 2
    SELECT 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 3
    DECLARE @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)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • 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!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply