Solved

equivalent syntax for lag or lead

Posted on 2013-10-31
5
673 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 48

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 48

Accepted Solution

by:
PortletPaul earned 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 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

18 Experts available now in Live!

Get 1:1 Help Now