Update the other columns which are for to have minimum of other 2 columns , difference between 2 columns , and others

oracle

I am working on data in oracle 11g as below :

subscriber_id |  count_s  |  count_t |   min_count   |  diff    | negative_diff   |  positive_diff

137606401        3          3              3        0          0                  0
102842273        5          4              4        1          0                  1
116638321        14        14             14        0          0                  0
98353910         53        53             53        0          0                  0
102869643        18        20             18       -2          2                  0
107144617        21        21             21        0          0                  0

Can you please help me with a logic to update min_count , diff , negative_diff , positive_diff considering we have values for subcriber_id , count_s and count_t ?

here , min_count should be minimum of (count_s , count_t) for that specific subscriber_id.

diff should be difference between count_s and count_t , which can be easily attained with [count_s – count_t]

negative_diff should be updated for subscribers which has -ve diff values otherwise value for this should be 0 [-ve sign should not be considered while updating]

similarly , positive_diff should be updated for subscirbers which has +ve diff values otherwise value shouild be 0

please suggest the with sql code snippet to be used to update min_count , negative_diff and positive_diff.

Note: Working in SQL

Best Answer

update t set
 min_count = least(count_s, count_t),
 diff = count_s - count_t,
 negative_diff = decode(sign(count_s - count_t), -1, count_s - count_t, 0),
 positive_diff = decode(sign(count_s - count_t),  1, count_s - count_t, 0)
;

Or just use virtual columns since you are on 11g:

create table t(
  subscriber_id number, 
  count_s number, 
  count_t number, 
  min_count as (least(count_s, count_t)),
  diff as (count_s - count_t), 
  negative_diff as (decode(sign(count_s - count_t), -1, count_s - count_t, 0)), 
  positive_diff as (decode(sign(count_s - count_t),  1, count_s - count_t, 0))
);
Related Question