CODEWITHSUNDEEP
sqlsybase
Jojo
Jojo

Reputation: 1117

Difference between rows of the same column if particular conditions are met

Im trying to create a new column called Diff that contains the difference between different rows of the same Column called Rep, which is an Integer.

My table looks as follows:

------------------------
security_ID | Date | Rep 
------------------------
2256        |202001|  0
2257        |202002|  1
2258        |202003|  2
2256        |202002|  3
2256        |202003|  5

For a particular security_ID I want to get the difference in Rep if the Date's, which are Integer, are different by 1 (for eg. with 202002-202001 = 1). For example, I want the output to be:

-------------------------------
security_ID | Date | Rep | Diff
-------------------------------
2256        |202001|  0  |  0
2257        |202002|  1  |  1 
2258        |202003|  2  |  2 
2256        |202002|  3  |  3 
2256        |202003|  5  |  2

With the last row being a Diff of 2, because the calculation would be 5-3 (for the Date 202003 and 202002, respectively) for the security_ID 2256.

Edit: Because Sybase doesn't have LAG() I tried the following:

SELECT security_ID, Date, Rep, 
MIN(Rep) OVER (PARTITION BY Date, security_ID rows between current row and 1 following) - Rep as "Diff"
from
my_table

But this does not give me the right answer. For example, with the last and penultimate row difference above Diff is 0 according to the above.

Thanks

Upvotes: 1

Views: 191

Answers (1)

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

Assuming the date column is always in increasing order we can use left join with self and bring the previous rep value and then calculate the difference outside. As,

select security_id,dt,rep,(rep-prev_rep) diff
  from
(
select t1.security_id,t1.dt,t1.rep,
       coalesce(t2.rep,0) prev_rep
  from mytable t1
  left join mytable t2
    on t1.security_id = t2.security_id
   and t2.dt = t1.dt - 1
)
order by rep;

Edit: addressing the query attempt by OP

If you can use window function as you have shown , you can modify the query as below,

select security_id
     , dt
     , rep
     , (rep-coalesce(max(rep) over (partition by security_id order by dt rows between unbounded preceding and 1 preceding),0)) diff
from mytable;
order by rep

Upvotes: 1

Related Questions