Minusing row values

UDA
Fan

Minusing row values

How do I minus one field with the previous one, eg,

VALUE
0 - field value should =0
17 - field value should = 17 (17 - 0)
23 - field value should = 6 (23 - 17)
25 - field value should = 2 (25 - 23)

2 REPLIES
Senior Apprentice

Re: Minusing row values

Hi quarry,
it's called a moving difference:

select
col,
min(col) over (order by col rows between 1 preceding and 1 preceding) as prev_val,
col - coalesce(prev_val,0) as moving_diff
from tab

Dieter
Enthusiast

Re: Minusing row values

A. Is it possible to to do this if the col I want the difference of is a Date and to have it returned as the # of days rather than the numerical difference of the system date (1071001 - 1070901). I would want 30 days returned, not 1000.

B. Is there a difference in terms of system resources by using MDIFF?

Thanks,