Finding the last month's values

  • Comments posted to this topic are about the item Finding the last month's values

  • As far as I can tell, there are actually two correct answers to this one. Both

    LEAD(currMonthSales) OVER (ORDER BY saleyear DESC, salemonth DESC) AS prevSales

    and

    LAG(currMonthSales) OVER (ORDER BY saleyear ASC, salemonth ASC) AS prevSales

    yield correct results, although the LAG version is possibly more inutitive.


    Just because you're right doesn't mean everybody else is wrong.

  • I agree with Rune Bivrin's comment. There are two correct answers, as far as I can tell.

    --> Steve Jones:  If we are incorrect, please provide test data that clearly demonstrates that we are not getting the right answer with the first choice.

    Thanks.

    • This reply was modified 9 months, 2 weeks ago by  Tom Wickerath.
    • This reply was modified 9 months, 2 weeks ago by  Tom Wickerath.
  • This was removed by the editor as SPAM

  • BAWAAA-HAAAA-HAAAA!!!  I'll get the popcorn! 😀

    --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".

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

  • Ok... so to those that are saying that answer #4 is also correct, that's incorrect.  Here's the output from answer #3 and answer #4.  #4 is incorrect if you pay attention to where the NULL appears in the output.  Also, the PrevSales is coming from the wrong month for the non-null entries if you accept the normal definition of "PrevSales".

    --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".

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

  • we are saying, that #1 and #3 are correct, not #3 and #4.

    LEAD with descending order is the same as LAG with ascending order (except that the returned rows are (usually - if it does not go parallel) are sorted ascending / descending.

    God is real, unless declared integer.

  • First choice works, too - LEAD...

  • Thomas Franz wrote:

    we are saying, that #1 and #3 are correct, not #3 and #4.

    LEAD with descending order is the same as LAG with ascending order (except that the returned rows are (usually - if it does not go parallel) are sorted ascending / descending.

    Ah... my apologies.  I'll have to blame it on insignificant levels of caffeine at the time.  Thank you for the correction.

    I agree that answer #1 also works.  It's also interesting what the differences in execution plans are for larger amounts of data although SET STATICS TIME,IO ON show little difference.  The following code is what I used to create a lot more data on my laptop if you want to play.  Of course, it's for performance testing only because it's seriously not something someone would actually need to do simply because of the full-monty range of dates that I used for the test.

       DROP TABLE IF EXISTS #MonthSales;
    WITH cteGenDate AS
    (
    SELECT SomeDate = DATEADD(mm,t.N,CONVERT(Date,'00010101'))
    FROM dbo.fnTally(0,DATEDIFF(mm,'00010101','99991231')) t
    )
    SELECT SaleYear = DATEPART(yy,SomeDate)
    ,SaleMonth = DATEPART(mm,SomeDate)
    ,CurrMonthSales = ABS(CHECKSUM(NEWID())%1000)
    INTO #MonthSales
    FROM cteGenDate
    ;

    The code from answer 1 removes one sort (as expected) but the sort then spills to TempDB whereas the code from answer 3 does not.

    This was interesting... thanks. you all.

    --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".

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

  • Cut/paste strikes again. I edited the wrong item and made #1 correct. I've changed it to be incorrect.

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

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