sql stmt

[rkonda99]

I have the data like this.

machine_id cumulative_hours
1000 200
1001 500
1002 900
---------------------------------------------

I want the result to be converted into non-cumulative values like this.

machine_id hours
1000 200
1001 300
1002 400

Essentially, after first row, every next row should have values equals to (current value-previous value).

I can do it in PL/SQL, but I prefer to do this in SQL statement for performance.

Any ideas..

Thanks.

[akskumar]

try this
select machine_id, cumulative_hours - nvl(lag(cumulative_hours) over (order by cumulative_hours asc), 0) as hours from table_name

hth

[rkonda99]

Wow, this works great. Thanks a lot for your feedback. I am not aware of some of the key words that you have used (i.e, lag, over). Now, I will go back to SQL book and try to figure this out.
Thanks again.

[akskumar]

LAG is a analytical function.

just check this URL

http://www.akadia.com/services/ora_a...functions.html

Thx
Aks

[rkonda99]

Thanks Aks for the link