November 2, 2017 at 4:18 am
I am working for a company that still uses Sql 2008 R2 and so i cant use Lead/Lag Functions.
I know the general workaround of sub queries with partitions etc to replicate what this does but i am working on a project that requires lots and lots of leads and lags to be used.
Therefore my question is has anybody come up with a smart way of replicating what a lead function does that could be called from a UDF etc that would make this process easier?
November 2, 2017 at 4:41 am
I generally add a RANK() OVER() in a CTE to the original table then you can use
Select ,B.OtherField
A.Field
FROM
CTE AS A
JOIN
CTE AS B ON B.KeyField = A.KeyField AND B.Rank = A.Rank + 1
Modify the JOIN predicate as necessary to make sure you include the first/last record if necessary
UDFs are likely to be very slow as they are hidden ReBAR (Row By Agonising Row). The performance of the CTE approach depends very much on your indexing strategy and whether the partition and order fields are in the index but is still likely to be faster. I also prefer it because it is obvious what the code is trying to do whereas the UDF acts like a black-box so is harder to understand
November 2, 2017 at 5:43 am
matthew.hoole - Thursday, November 2, 2017 4:18 AMI am working for a company that still uses Sql 2008 R2 and so i cant use Lead/Lag Functions.
I know the general workaround of sub queries with partitions etc to replicate what this does but i am working on a project that requires lots and lots of leads and lags to be used.
Therefore my question is has anybody come up with a smart way of replicating what a lead function does that could be called from a UDF etc that would make this process easier?
Probably easiest to answer Matthew if you can provide an actual example?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2017 at 5:54 am
I almost think the OP is looking for a generic tool for every scernario, i.e. making their own LAG/LEAD function. That isn't going to happen.if you wanted to use a UDF, you'd need to be able to feed it the dataset, along with ordering details, partition, etc. That's not going to be possible.
With the little information we've got, Aaron is on the right track. you're going to need to use a rank/row_number function inside a CTE and then (LEFT) JOIN onto the current row +/- x. This is going to be bespoke for every query, and if you need several LEAD/LAG results based on different criteria in a single statement, it's going to become a costly query.
Otherwise, if the functionality of LEAD/LAG is that important to your business, I think it would be time to start considering an upgrade. 2008 is 9 years old now, and only has a couple of year's support left (some business can take years to migrate, so you probably want tog et the ball rolling soon anyway). There's a lot of great new features that have been made available in since 2008, so you'll be getting a lot more than just a couple of extra features.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 2, 2017 at 6:11 pm
Mainstream Support for SQL Server 2008 R2 ended in 2014.
November 3, 2017 at 3:08 am
Joe Torre - Thursday, November 2, 2017 6:11 PMMainstream Support for SQL Server 2008 R2 ended in 2014.
Extended support still exists. Otherwise 2012 would not longer be supported either.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply