Using LAG

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Carlo Romagnano

    SSC-Insane

    Points: 21906

    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

  • free_mascot

    One Orange Chip

    Points: 27168

    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

  • Ed Wagner

    SSC Guru

    Points: 286975

    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.

  • Xavon

    SSCrazy

    Points: 2280

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

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

  • RK Mandava

    SSCarpal Tunnel

    Points: 4153

    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

  • Hany Helmy

    SSChampion

    Points: 13436

    [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:

  • RLilj33

    SSCrazy

    Points: 2153

    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.

  • g.britton

    SSChampion

    Points: 13689

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • Nevyn

    SSChampion

    Points: 14210

    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.

  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

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

  • Ken Wymore

    SSCoach

    Points: 16523

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

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