CODEWITHSUNDEEP
mysqlsqlsnowflake-cloud-data-platform
sravas
sravas

Reputation: 35

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

Upvotes: 1

Views: 1628

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use the range option for window definition to do this. It is very handy for variable ranges:

SELECT t1.*,
       MIN(CURR_STRT_DT) OVER (ORDER BY CURR_STRT_DT
                               RANGE BETWEEN interval 1 day FOLLOWING AND UNBOUNDED FOLLOWING
                              ) as CURR_END_DT
FROM Table1 t1
ORDER BY CURR_STRT_DT;

This should be the simplest and most efficient method.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

enter image description here

Upvotes: 2

Tajinder
Tajinder

Reputation: 2338

Hoping, I understood your question correctly, MySQL LAG and LEAD function can help you to achieve the same.

Please check below query.

select CURR_STRT_DT,CUUR_END_DT,product,
 Lead(CURR_STRT_DT) OVER (ORDER BY CURR_STRT_DT) NEXT_SRT_DT,
 Lead(CUUR_END_DT) OVER (ORDER BY CUUR_END_DT) NEXT_END_DT
from tab_name;

You can also partition the data by product. I have posted the tutorial link, you can check.

Tutorial: http://www.mysqltutorial.org/mysql-window-functions/mysql-lag-function/

Upvotes: 0

Related Questions