Alternate of Lead Lag Function in SQL Server 2008

Alternate of lead lag function in SQL Server 2008

In your case, the ids appear to be numeric, you can just do a self-join:

select t.*
from table t join
     table tnext
     on t.id = tnext.id - 1 and
        t.StatusId = 1 and
        tnext.StatusId = 6 and
        datediff(second, t.MinStartTime, tnext.MinStartTime) < 60;

This isn't quite the same minute. It is within 60 seconds. Do you actually need the same calendar time minute? If so, you can do:

select t.*
from table t join
     table tnext
     on t.id = tnext.id - 1 and
        t.StatusId = 1 and
        tnext.StatusId = 6 and
        datediff(second, t.MinStartTime, tnext.MinStartTime) < 60 and
        datepart(minute, t.MinStartTime) = datepart(minute, tnext.MinStartTime);

How can we use LAG() in SQL server 2008 R2?

You can use APPLY :

SELECT (CASE WHEN d.[Department Name] = Prev_Dept
             THEN '' ELSE d.[Department Name]
        END) AS DepartmentName
FROM department d OUTER APPLY
     ( SELECT TOP (1) d1.[Department Name] AS Prev_Dept
       FROM department d1
       WHERE d1.? < d.?
       ORDER BY ? DESC
     ) AS Pd;

? indicates identify column that identifies unique sequence of data.

What should be the alternative for lead and lag for SQL Server 2000 to 2008

this worked for me

"WITH CTE AS(
                       SELECT rownum = ROW_NUMBER() OVER(ORDER BY p.InvNo),
                       p.InvNo FROM Invoice1 p
                     )
                   SELECT
                   prev.InvNo prev_invNo,
                       CTE.InvNo,
                       nex.InvNo next_invNo
                   FROM CTE
                   LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
                   LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
                   where CTE.InvNo = " + invoiceNumber + ""

How to use LAG FUNCTION in SQL SERVER 2008

Unfortunately LAG is not available to versions prior to 2012 in SQL Server. You have to use ROW_NUMBER with a self join instead:

;With Quote as (
   SELECT SID, SHEET,Code, [Date], Data,
          ROW_NUMBER() OVER (ORDER BY [Date]) AS rn
   FROM RMQ_DATA 
   WHERE [SHEET] IN (0)
), Quote_LAG AS (
   SELECT q1.SID, q1.SHEET, q1.Code, q1.[Date], q1.Data,
          q2.Data AS LastMonthData
   FROM Quote AS q1
   LEFT JOIN Quote AS q2 ON q1.rn = q2.rn + 1
)
... rest of the query here

Using LAG/LEAD with missing PK on alternate rows

With @Honeybadger's suggestion, I implemented the IDENTITY(1,1) attribute when creating the temporary landing table. This captured the natural row sequence as I loaded the data. Works like a charm!

CREATE TABLE #EMP 
(
 EMPID INT
, JOBTITLE NVARCHAR(19)
, SAL INT
, ROW_SEQ INT IDENTITY(1,1) 
) 



INSERT INTO #EMP 
VALUES (1234, 'Sales', 56000), 
       (NULL, NULL, 54000),
       (1235, 'Marketing', 72000),
       (NULL, NULL, 70500) 
 
SELECT * FROM (
SELECT 
    EMPID, 
    JOBTITLE,
    SAL,
    LEAD(SAL,1,0) OVER (ORDER BY ROW_SEQ ) AS PREV_SAL
FROM  #EMP )  T WHERE EMPID IS NOT NULL 

Outputs:

EMPID   JOBTITLE    SAL     PREV_SAL
1234    Sales       56000   54000
1235    Marketing   72000   70500

---

Related Topics