CODEWITHSUNDEEP
sqldatabaseoracle-database
Sayandip Ghatak
Sayandip Ghatak

Reputation: 303

Compare row wise value change in Oracle using SQl only

I have a table like this ,

ID Quarter Risk
00001 Q0 2
00001 Q1 3
00001 Q2 1
00001 Q3 1
00001 Q4 2

1 means Lower Risk and 3 means Higher Risk . And Q0 means 4th Quarter of previous year .

I want a output like this :

ID Quarter Risk Status
00001 Q0 2 null
00001 Q1 3 Upgraded
00001 Q2 1 Downgraded
00001 Q3 1 No Change
00001 Q4 2 Upgraded

If the Risk is greater than that of its previous quarter then it is Upgraded and otherwise Downgraded . If the value remains same then No Change.

I want to achieve this using only SQL in Oracle 10G. I cannot use PL/SQL.

Can anyone please help me on this?

Upvotes: 1

Views: 205

Answers (2)

George Joseph
George Joseph

Reputation: 5922

The same can be achieved using lag function which is available in Oracle 10g.

select id
      ,quarter
      ,risk
      ,case when risk - lag(risk) over(order by quarter) <0 then 
                 'Downgraded'
            when risk - lag(risk) over(order by quarter) =0 then 
                 'No change'
            when risk - lag(risk) over(order by quarter) >0 then 
                 'Upgraded'
       end as status 
  from table

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use lag() :

select t.*,
       (case when Risk > prev_rsk
             then 'Upgraded'
             when Risk < prev_rsk
             then 'Downgraded'
             when Risk = prev_rsk
             then 'No Change'
        end) as Status
from (select t.*,
             lag(Risk) over (partition by id order by Quarter) as prev_rsk
      from table t
     ) t;

Upvotes: 4

Related Questions