Solved

equivalent syntax for lag or lead

Posted on 2013-10-31
5
648 Views
Last Modified: 2013-10-31
Hello

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

Regards
0
Comment
Question by:PHIL Sawyer
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:jmanishbabu
Comment Utility
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
 

Author Comment

by:PHIL Sawyer
Comment Utility
SQL SERVER 2008 does not support LAG LEAD functions? - or am I missing something
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
 

Author Comment

by:PHIL Sawyer
Comment Utility
The rownum was the clue and that logic has worked for me  - Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server RDS clr assembly 4 33
replication - alerts? 4 19
Sql query for filter 12 20
Usage Scenarios for Extended Events? 1 8
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now