Ryan Gomes Ryan Gomes - 3 months ago 8
SQL Question

SQL Creating a new column with previous dates in new column

I would Like get below result in SQL Server (Color Coded)

The Column Required is How I would like my Dates to be:

enter image description here

Or this link http://imgur.com/easxkMH

The new dates have to be specific to that ID

I was thinking of creating a new column of sequence(1, 2, 3,...) number per ID and another column where it is incremented by 1 (NULL, 1, 2, 3) and then doing a self left join.

Please advice on any other procedure and help with the code

Thanks

Answer

You can use LEAD and LAG

Syntax

LEAD (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

SELECT    Id, Date
        , LEAD(Date) OVER (ORDER BY Id) AS [Next Date]
        , LAG(Date) OVER (ORDER BY Id) AS [Prev Date]
        , LEAD(Date, 2) OVER (ORDER BY Id) AS [2nd Next Date]
        , LAG(Date, 2) OVER (ORDER BY Id) AS [2nd Prev Date]
        , LEAD(Date, 2, 0) OVER (ORDER BY Id) AS [2nd Next Date]
        , LAG(Date, 2, 0) OVER (ORDER BY Id) AS [2nd Prev Date]
FROM    @Test_table