Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1576
  • Last Modified:

equivalent syntax for lag or lead

Hello

Using sql server 2008 and want to know the sql syntax equivalent for lag and lead.

Regards
0
PHIL Sawyer
Asked:
PHIL Sawyer
  • 2
  • 2
1 Solution
 
jmanishbabuCommented:
WITH shifted_timeseries AS (
    SELECT event_time,
           value,
           LAG(value) OVER (ORDER BY event_time) AS lagged_value
        FROM timeseries
)
SELECT event_time AS change_time, value AS new_value
FROM shifted_timeseries
    WHERE value != lagged_value;

Check this below link

http://blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/
0
 
PHIL SawyerAuthor Commented:
SQL SERVER 2008 does not support LAG LEAD functions? - or am I missing something
0
 
PaulCommented:
often row_number() can be used to generate a method to join tables so that the effect of lead/lag.

do you have a specific scenario to share?

(& yes lead/lag does not exist in sql 2008)
0
 
PaulCommented:
here's an example (simplified from a previous question) hopefully it will make sense:
;WITH
  CTE AS (
        SELECT
               TransNo
             , TransType
             , TransDate
             , Prod
             , Qty
             , Cost
             , row_number() over (partition BY TransNo ORDER BY TransDate ASC) AS rn
        FROM PurchTransactions
      )
SELECT
      t.TransNo
    , t.TransType
    , t.TransDate
    , t.Prod
    , t.Qty
    , t.Cost
    , t.rn
    , CASE WHEN nxt.TransDate <> t.TransDate THEN nxt.rn ELSE NULL END AS diff_date
    , CASE WHEN nxt.qty       <> t.qty       THEN nxt.rn ELSE NULL END AS diff_qtys
    , CASE WHEN nxt.cost      <> t.cost      THEN nxt.rn ELSE NULL END AS diff_cost
FROM CTE AS t
LEFT JOIN CTE AS nxt ON t.TransNo = nxt.TransNo
                    AND (t.rn + 1) = nxt.rn --<< similar to lead

Open in new window

the row_number gives us a way to perform a self-join so that (here) the "next" record is joined and hence calculations performed in a row mimic the effect of lead
0
 
PHIL SawyerAuthor Commented:
The rownum was the clue and that logic has worked for me  - Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now