0

I am working for a company that still uses MS SQL Server 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?

jarlh
  • 42,561
  • 8
  • 45
  • 63
arbores
  • 1

1 Answers1

4

In SQL Server 2008, you can use apply:

select t.*, tprev.*
from t outer apply
     (select top 1 tprev.*
      from t tprev
      where tprev.? = t.? and   -- partitioning columns
            tprev.? < t.?       -- order by columns
      order by ? desc           -- order by columns, ordering reversed
     ) tprev;

This will substitute for a lag of 1 (similar code handles lead()). It can handle multiple columns at the same time. This covers many common uses of lag().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786