CODEWITHSUNDEEP
sqlsql-serversql-server-2005
ANSH
ANSH

Reputation: 21

Alternative for lead() function in SQL Server 5

I want the same output as lead function do for window function. I want to do it for SQL Server version 5.0

Table Name: Table

EventID  CID  Date
123      1    01-12-2020
123      2    01-12-2020
123      3    01-12-2020
345      2    05-12-2020
345      4    05-12-2020
456      1    07-12-2020
456      4    07-12-2020
567      1    08-12-2020

Output

ID      CID     Date       ColumnA
123      1    01-12-2020    07-12-2020
456      1    07-12-2020    08-12-2020
567      1    08-12-2020    Null
123      2    01-12-2020    05-12-2020
345      2    05-12-2020    Null
123      3    01-12-2020    Null
345      4    05-12-2020    07-12-2020
456      4    07-12-2020    Null

Upvotes: 1

Views: 2919

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522050

You may use a correlated subquery in lieu of not having access to the LEAD() function:

SELECT
    EventID AS ID,
    CID,
    Date,
    (SELECT TOP 1 t2.Date FROM yourTable t2
     WHERE t2.CID = t1.CID AND t2.Date > t1.Date
     ORDER BY t2.Date) AS ColumnA
FROM yourTable t1
ORDER BY
    CID,
    Date;

screen capture from demo link below

Demo

Edit:

You tagged your question for SQL Server, explaining why you received this and the other answers. If your RDBMS really be MySQL, then use this version:

SELECT
    EventID AS ID,
    CID,
    Date,
    (SELECT t2.Date FROM yourTable t2
     WHERE t2.CID = t1.CID AND t2.Date > t1.Date
     ORDER BY t2.Date LIMIT 1) AS ColumnA
FROM yourTable t1
ORDER BY
    CID,
    Date;

Upvotes: 2

gotqn
gotqn

Reputation: 43646

Try this:

WITH DataSource AS
(
    SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY [CID] ORDER BY [Date] ASC) AS [RowID]
    FROM [Table] 
)
SELECT DS1.EventID  
      ,DS1.[CID]
      ,DS1.[Date]
      ,DS2.[Date] AS ColumnA
FROM DataSource DS1
LEFT JOIN DataSource DS2
    ON DS1.[RowID] = DS2.[RowID] - 1

Upvotes: 0

Related Questions