Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

equivalent syntax for lag or lead

Posted on 2013-10-31
5
Medium Priority
?
1,139 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
ID: 39613646
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
ID: 39613705
SQL SERVER 2008 does not support LAG LEAD functions? - or am I missing something
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39613731
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 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39613745
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
ID: 39613856
The rownum was the clue and that logic has worked for me  - Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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, …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

886 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