Skip to content
Advertisement

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

Advertisement

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      
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement