Usage of CTE - Trick with Dates

  • SoCal_DBD

    Hall of Fame

    Points: 3098

    Jeff Moden - Friday, March 3, 2017 6:07 PM

    SoCal_DBD - Friday, March 3, 2017 3:21 PM

    Dan Kelley - Friday, March 3, 2017 2:16 PM

    I figured I'd give it a go before I looked at the answers and here's what I came up with. :laugh: It's probably terribly inefficient, I'm new at this and didn't know about tally tables or CTE's 🙂

    DECLARE @TotalRowcount INT = (SELECT SUM(Month_Count) FROM abc)
    DECLARE @abc_row INT = 1
    DECLARE @Payment DECIMAL(10,2) = (SELECT Payment FROM abc WHERE SeqNo = @abc_row)
    DECLARE @MonthsToAdd INT = 0

    WHILE (SELECT COUNT(*) FROM def) < @TotalRowcount
      BEGIN
     IF @MonthsToAdd = (SELECT Month_Count FROM abc WHERE  SeqNo = @abc_row)
       BEGIN
      SET @abc_row += 1;
      SET @MonthsToAdd = 0;
      SET @Payment = (SELECT Payment FROM abc WHERE SeqNo = @abc_row);
       END;
      
     INSERT  INTO def (SeqNo,Date_Field,Payment)
     SELECT @abc_row SeqNo
         ,DATEADD(M, @MonthsToAdd, abc.Date_Field) Date_Field
         ,@Payment Payment
     FROM abc
     WHERE SeqNo = @abc_row;
      
     SET @MonthsToAdd += 1;
      END;

    Hi Dan and welcome to this (awesome) forum!  Good stab, but that still is operating as a cursor, or more affectionately known as "RBAR" (row by agonizing row).  I'll save you the time looking through the responses on this post (I see there are many pages and it is rather old), but here is a solution that implements a "tally table".  These things are magical.  I can highly recommend reading up on anything written by author extraordinaire on this forum, Jeff Moden.  His article on the tally table will blow your mind and can be found here:  http://www.sqlservercentral.com/articles/T-SQL/62867/

    I took the example and created temp tables to do the work, but here is an example of a tally table solution for this:

    Happy coding!

    Lisa

    There's no need for the first SELECT of the UNION ALL nor the UNION ALL, Lisa.  Give it a shot.

    Awww Jeff..... I hang my head in SHAME!!!  :crying: Original post revised as I don't want that out there for eternity.....  Do I get a pass for doing it late on a Friday when I had weekend-itis??

  • Dan Kelley

    SSC Enthusiast

    Points: 120

    SoCal_DBD - Monday, March 6, 2017 8:58 AM

    Jeff Moden - Friday, March 3, 2017 6:07 PM

    There's no need for the first SELECT of the UNION ALL nor the UNION ALL, Lisa.  Give it a shot.

    Awww Jeff..... I hang my head in SHAME!!!  :crying: Original post revised as I don't want that out there for eternity.....  Do I get a pass for doing it late on a Friday when I had weekend-itis??

    Thank you Jeff and Lisa for the awesome tips! Lisa, in your revised post, is there any reason why you couldn't (or shouldn't) just change the small tally table seed to 0 instead of subtracting 1 in the DATEADD function? I know it's probably a petty difference, but I'm curious 🙂

    IF OBJECT_ID('tempdb..#tally', 'U') IS NOT NULL
    DROP TABLE #tally

    -- Create a temp table version of a small tally table

    SELECT TOP 1000 IDENTITY(INTEGER, 0, 1) AS N
    INTO #Tally
    FROM sys.[objects]
    CROSS JOIN sys.columns

    INSERT INTO #def (SeqNo, Date_Field, Payment)
    SELECT a.SeqNo, DATEADD(M, t.N, a.Date_Field) Date_Field, a.Payment
    FROM #abc a
    CROSS JOIN #Tally t
    WHERE t.N < a.Month_Count

    SELECT * FROM #def
    ORDER BY SeqNo, Date_Field

    Also, my apologies for reviving a two year old thread! As you said, it was Friday evening so it's understandable. :laugh:

  • Jeff Moden

    SSC Guru

    Points: 997308

    SoCal_DBD - Monday, March 6, 2017 8:58 AM

    Awww Jeff..... I hang my head in SHAME!!!  :crying: Original post revised as I don't want that out there for eternity.....  Do I get a pass for doing it late on a Friday when I had weekend-itis??

    Anyone who tries to help someone else, especially with weekend-itis looming, is aces in my book.  😉

    --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)

  • EliG

    SSC Veteran

    Points: 264

    Interesting read - greatly appreciated.
    One point to note: since the second query in the CTE (after the UNION ALL) already limits the entries to be within range, there is no need for the second where clause (in the INSERT statement), as dates which fall out of that range have already been eliminated.

  • Jeff Moden

    SSC Guru

    Points: 997308

    EliG - Monday, March 6, 2017 11:56 AM

    Interesting read - greatly appreciated.
    One point to note: since the second query in the CTE (after the UNION ALL) already limits the entries to be within range, there is no need for the second where clause (in the INSERT statement), as dates which fall out of that range have already been eliminated.

    Which code are you referring to?  I ask because there's no need for a CTE and there's no need for a UNION ALL.  Heh... nor is there a need for the WHILE. 😉

    --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)

  • EliG

    SSC Veteran

    Points: 264

    The original article.

  • Jeff Moden

    SSC Guru

    Points: 997308

    EliG - Monday, March 6, 2017 12:20 PM

    The original article.

    I'd recommend avoiding the rCTE in the original article.  Please refer to the following article, which shows just how bad rCTEs that calculate a sequence actually are compared to 3 other simpler methods.
    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --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)

  • EliG

    SSC Veteran

    Points: 264

    Jeff Moden - Monday, March 6, 2017 12:30 PM

    EliG - Monday, March 6, 2017 12:20 PM

    The original article.

    I'd recommend avoiding the rCTE in the original article.  Please refer to the following article, which shows just how bad rCTEs that calculate a sequence actually are compared to 3 other simpler methods.
    http://www.sqlservercentral.com/articles/T-SQL/74118/

    Jeff,

    My point wasn't whether or not to use an rCTE, rather, it was a point made on an extraneous piece of processing contained in an article written to demonstrate a method of reducing processing.

  • Jeff Moden

    SSC Guru

    Points: 997308

    EliG - Monday, March 6, 2017 12:57 PM

    Jeff Moden - Monday, March 6, 2017 12:30 PM

    EliG - Monday, March 6, 2017 12:20 PM

    The original article.

    I'd recommend avoiding the rCTE in the original article.  Please refer to the following article, which shows just how bad rCTEs that calculate a sequence actually are compared to 3 other simpler methods.
    http://www.sqlservercentral.com/articles/T-SQL/74118/

    Jeff,

    My point wasn't whether or not to use an rCTE, rather, it was a point made on an extraneous piece of processing contained in an article written to demonstrate a method of reducing processing.

    Understood.  My point was that your point is a bit moot because you shouldn't be using the rCTE method.

    --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 - 61 through 69 (of 69 total)

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