Using LAG

  • Comments posted to this topic are about the item Using LAG

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • But note that supplying an offset only produces the expected results if you can be sure that there is exactly one row per month in the sales table. (Or, if you use the PARTITION BY clause, exactly one row per distinct set of values of the partition columns and per month).

    LAG simply counts rows, it does not look at the actual values.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (4/15/2015)


    But note that supplying an offset only produces the expected results if you can be sure that there is exactly one row per month in the sales table. (Or, if you use the PARTITION BY clause, exactly one row per distinct set of values of the partition columns and per month).

    LAG simply counts rows, it does not look at the actual values.

    +1

  • Carlo Romagnano (4/15/2015)


    Hugo Kornelis (4/15/2015)


    But note that supplying an offset only produces the expected results if you can be sure that there is exactly one row per month in the sales table. (Or, if you use the PARTITION BY clause, exactly one row per distinct set of values of the partition columns and per month).

    LAG simply counts rows, it does not look at the actual values.

    +1

    +1

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • This was removed by the editor as SPAM

  • Nice question. Of all the things we get when we upgrade to 2012 or 2014, LEAD and LAG are the ones I'm looking forward to the most. Yes, I know, it's 2015.

  • free_mascot (4/15/2015)


    Carlo Romagnano (4/15/2015)


    Hugo Kornelis (4/15/2015)


    But note that supplying an offset only produces the expected results if you can be sure that there is exactly one row per month in the sales table. (Or, if you use the PARTITION BY clause, exactly one row per distinct set of values of the partition columns and per month).

    LAG simply counts rows, it does not look at the actual values.

    +1

    +1

    Triple ditto; the only reason I picked the right answer was because the other three were definitely wrong, so I guessed that was the author's intent.

  • Thanks for the question. I didn't realize this even existed.

  • Xavon (4/15/2015)


    free_mascot (4/15/2015)


    Carlo Romagnano (4/15/2015)


    Hugo Kornelis (4/15/2015)


    But note that supplying an offset only produces the expected results if you can be sure that there is exactly one row per month in the sales table. (Or, if you use the PARTITION BY clause, exactly one row per distinct set of values of the partition columns and per month).

    LAG simply counts rows, it does not look at the actual values.

    +1

    +1

    Triple ditto; the only reason I picked the right answer was because the other three were definitely wrong, so I guessed that was the author's intent.

    +1

  • [Using XML in Computed Columns], hmmm, that`s somthin new. Thanx

    This is 4 the Computed Column question, don`t know how it came here :hehe::cool:

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Ed Wagner (4/15/2015)


    Nice question. Of all the things we get when we upgrade to 2012 or 2014, LEAD and LAG are the ones I'm looking forward to the most. Yes, I know, it's 2015.

    +1 - Our minimum support version is 2008.

    And thanks Aunt Kathi for the question as I wasn't aware of the analytic functions.

  • Hugo Kornelis (4/15/2015)


    But note that supplying an offset only produces the expected results if you can be sure that there is exactly one row per month in the sales table. (Or, if you use the PARTITION BY clause, exactly one row per distinct set of values of the partition columns and per month).

    LAG simply counts rows, it does not look at the actual values.

    Not only that, but you need an ORDER BY clause. Otherwise, there is no guarantee that 20140101 is exactly 12 rows before 20150101

    Gerald Britton, Pluralsight courses

  • Hugo Kornelis (4/15/2015)


    But note that supplying an offset only produces the expected results if you can be sure that there is exactly one row per month in the sales table. (Or, if you use the PARTITION BY clause, exactly one row per distinct set of values of the partition columns and per month).

    LAG simply counts rows, it does not look at the actual values.

    While we're at it, it also only works if you order by the months in ascending order and if the query that has a total per month is not missing any months.

  • Outer join to a calendar table to ensure that there are no missing months, order by the date, and it should work.

  • Luckily there was only one correct answer so I didn't have to play the "guess the author's intention" game. 😛 LEAD and LAG have been very helpful in simplifying some of our queries since we upgraded to 2012. Thanks for the question Kathi.

Viewing 15 posts - 1 through 15 (of 26 total)

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