CODEWITHSUNDEEP
sqlsql-server
Houssem Timoumi
Houssem Timoumi

Reputation: 53

How can I print the second row value in the first row column?

I have fetching data as follows:

start_date       end_date      amount   
12/10/2020          -          1800000
12/18/2020          -          1200000
01/18/2021          -          1000000

I would like to print the start date of the second row to the end date of the first row for X rows I am fetching so that the table becomes as follows:

start_date       end_date      amount   
12/10/2020      12/18/2020     1800000
12/18/2020      01/18/2021     1200000
01/18/2021          -          1000000

Thanks everyone!

Upvotes: 0

Views: 588

Answers (4)

Vedaraj Asirvatham
Vedaraj Asirvatham

Reputation: 21

Used the sample table and selected the data. Please change the SQL query as per your need.

DECLARE @Range TABLE (
    start_date DATETIME
    ,end_date DATETIME
    ,amount INT
    )

INSERT @Range
SELECT '12/10/2020'
    ,NULL
    ,1800000

UNION ALL

SELECT '12/18/2020'
    ,NULL
    ,1200000

UNION ALL

SELECT '01/18/2021'
    ,NULL
    ,1000000

SELECT start_date
    ,LEAD(start_date, 1) OVER (
        ORDER BY start_date
        ) AS end_date
    ,amount
FROM @Range;

Upvotes: 1

Thiyagu
Thiyagu

Reputation: 1330

Another option for LEAD by using Recursive CTE

DECLARE @T TABLE(start_date   DATE,    end_date   DATE,     amount   INT)

Insert into @T VALUES
('12/10/2020',NULL,1800000),
('12/18/2020',NULL,1200000),
('01/18/2021',NULL,1000000)

;WITH CTE AS(
                   SELECT rownum = ROW_NUMBER() OVER(ORDER BY End_Date),amount,Start_Date,End_Date
                   FROM @T
                 )
               SELECT
               CTE.start_date,CTE.amount,Nex.start_date AS[End Date]
               FROM CTE
               LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
               LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1

Upvotes: 0

leftjoin
leftjoin

Reputation: 38290

Use lead, if you have no column for partition by, use only order by in the over()

   select start_date, 
           lead(start_date) over (partition by ... order by start_date) as end_date, 
           amount
   ...

Upvotes: 1

Popeye
Popeye

Reputation: 35900

Use LEAD as follows:

select start_Date,
       lead(start_date) over (order by start_Date) as end_Date
       amount
  from your_Table t

Upvotes: 2

Related Questions