Using LAG

Question

Using LAG

Kathi Kellenberger

SSChampion

Points: 11811
More actions
April 14, 2015 at 10:30 pm

#316645

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
April 15, 2015 at 2:43 am

This was removed by the editor as SPAM

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

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

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: 22680 More actions · Answer 2

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: 27169 More actions · Answer 3

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Xavon SSCrazy Points: 2280 More actions · Answer 5

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 More actions · Answer 6

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

RK Mandava SSCarpal Tunnel Points: 4153 More actions · Answer 7

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: 13921 More actions · Answer 8

[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

RLilj33 SSCrazy Points: 2153 More actions · Answer 9

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: 13863 More actions · Answer 10

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

Nevyn SSChampion Points: 14210 More actions · Answer 11

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 More actions · Answer 12

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

Ken SSCoach Points: 17191 More actions · Answer 13

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.