SQL Function: Get next value in row

I’m looking for an SQL way to get the value from the next row.

   The data I have looks like:

CURR_STRT_DT    CUUR_END_DT      product 
  6/30/2019      9/28/2019         A
  6/30/2019      9/28/2019         A
  9/29/2019      12/28/2019        B
  12/29/2019     3/28/2020         C
  3/31/2019      6/29/2019         D

I need output as

 CURR_STRT_DT   CUUR_END_DT product   NEXT_SRT_DT   NEXT_END_DT
  6/30/2019      9/28/2019     A      9/29/2019      12/28/2019
  6/30/2019      9/28/2019     A      9/29/2019      12/28/2019
  9/29/2019      12/28/2019    B      12/29/2019     3/28/2020
  12/29/201      3/28/2020     C      3/31/2019      6/29/2019
  3/31/2019      6/29/2019     D      null            null

I tried to use lead function , last value and rank() all function but no use of it . can any one please help me to achieve above task

Answer

You can’t use Lead because you have a tie. So the next one is actually the same date

SQL DEMO

WITH cte as (
    SELECT *,  dense_rank() over (ORDER BY CURR_STRT_DT) as rn
    FROM Table1
)    
SELECT c1.*, 
      (SELECT `CURR_STRT_DT` FROM cte c2 WHERE c2.rn = c1.rn + 1 ) as `CURR_STRT_DT`,
      (SELECT `CUUR_END_DT` FROM cte c2 WHERE c2.rn = c1.rn + 1 ) as `CUUR_END_DT`
FROM cte c1;

OUTPUT

Advertisement

Answer