SQL – Compare rows based on date and transpose difference

Scenario:

Table ‘HIST’

RID	VALUE	HIST_DATE
1	V111	2019-01-01
1	V112	2020-02-11
1	V112	2020-03-08
1	V113	2020-04-11
1	V114	2021-03-15
2	V211	2020-04-11
2	V211	2021-03-16
3	V311	2019-05-01
3	V312	2020-01-01

EXPECTED OUTPUT:

RID	VALUE_OLD	VALUE_NEW
1	V113	V114

I want to display difference between rows in column ‘VALUE’ grouping by RID where the HIST_DATE is between ‘2020-03-31’ and ‘2021-04-01’

Then transpose the different values in Column ‘VALUE_OLD’ AND ‘VALUE_NEW’.

Note : Assuming only 2 different values can occur between those dates

Oracle SQL

Answer

Another option is to use LAG/LEAD analytics functions :

• LAG returns value from a previous row in the table.
• LEAD returns value from the next row in the table.

select rid
    , LAG(hist_date) OVER(partition by rid order by hist_date ) as HIST_DATE_OLD 
    , hist_date  as HIST_DATE
    , LAG(value) OVER(partition by rid order by hist_date ) as VALUE_OLD 
    , value as VALUE_NEW
from hist

select rid
    , lead(hist_date) OVER(partition by rid order by hist_date desc) as HIST_DATE_OLD 
    , hist_date  as HIST_DATE
    , lead(value) OVER(partition by rid order by hist_date desc) as VALUE_OLD 
    , value as VALUE_NEW
from hist
order by rid, hist_date

Then you can filter and format following your needs

select rid, VALUE_OLD, VALUE_NEW from (
    select rid
       , TO_CHAR(lead(hist_date) OVER(partition by rid order by hist_date desc), 'YYYY-MM-DD') as HIST_DATE_OLD 
       , TO_CHAR(hist_date , 'YYYY-MM-DD') as HIST_DATE
       , lead(value) OVER(partition by rid order by hist_date desc) as VALUE_OLD 
       , value as VALUE_NEW
    from hist
    where HIST_DATE between '2020-03-31' and '2021-04-01'
    and   rid = 1  
order by rid, hist_date
)
where HIST_DATE_OLD IS NOT NULL

RID VALUE_OLD VALUE_NEW 
--- --------- --------- 
  1 V113      V114          

select * from (
    select rid
       , TO_CHAR(lead(hist_date) OVER(partition by rid order by hist_date desc), 'YYYY-MM-DD') as HIST_DATE_OLD 
       , TO_CHAR(hist_date , 'YYYY-MM-DD') as HIST_DATE
       , lead(value) OVER(partition by rid order by hist_date desc) as VALUE_OLD 
       , value as VALUE_NEW
    from hist
order by rid, hist_date
)
where HIST_DATE between '2020-03-31' and '2021-04-01'
    and   rid = 1  

RID HIST_DATE_OLD HIST_DATE  VALUE_OLD VALUE_NEW 
--- ------------- ---------- --------- --------- 
  1 2020-03-08    2020-04-11 V112      V113      
  1 2020-04-11    2021-03-15 V113      V114      

take care about the filter inside and outside the main SELECT

• outside, you are filtering after doing the LAG/LEAD operation (above)
• inside, you are filtering before doing the LAG/LEAD operation (below)

RID HIST_DATE_OLD HIST_DATE  VALUE_OLD VALUE_NEW 
--- ------------- ---------- --------- --------- 
  1               2020-04-11           V113      
  1 2020-04-11    2021-03-15 V113      V114