-
sql stmt
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.
-
try this
select machine_id, cumulative_hours - nvl(lag(cumulative_hours) over (order by cumulative_hours asc), 0) as hours from table_name
hth
-
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.
-
LAG is a analytical function.
just check this URL
http://www.akadia.com/services/ora_a...functions.html
Thx
Aks
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|