Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to get previous non-zero value in query?

satnamApr 24 2014 — edited Apr 24 2014

I have data set like below:

ELEMENT_KEYDayHourVolume
2435701-01-201410
2435701-01-20142200
2435701-01-201430
2435701-01-201440
2435701-01-201450
2435701-01-201460
2435701-01-201470
2435701-01-201480
2435701-01-20149900
2435701-01-2014100
2435701-01-2014110
2435701-01-2014120
2435701-01-2014130
2435701-01-2014140
2435701-01-2014150
2435701-01-2014160
2435701-01-2014170
2435701-01-2014180
2435701-01-2014190
2435701-01-2014202000
2435701-01-2014210
2435701-01-2014220
2435701-01-2014230
2435701-01-2014240

Can some one help me to write query that can display a "Previous Non-ZERO value" like bleow output?

I try to write a query using Oracle analytical LAG function but I do not know how to modify LAG parameters so that it will find previous non-zero value.

SELECT elemnt_key, day, hour, volume,

             LAG(volume, 1, 99) OVER (PARTITION BY element_key ORDER BY element_key, day, hour ASC NULLS LAST) prev_non_zero_volume

  FROM  elm_table;

ELEMENT_KEYDayHourVolumeprevious_non_zero_volume
2435701-01-2014100
2435701-01-20142200200
2435701-01-201430200
2435701-01-201440200
2435701-01-201450200
2435701-01-201460200
2435701-01-201470200
2435701-01-201480200
2435701-01-20149900900
2435701-01-2014100900
2435701-01-2014110900
2435701-01-2014120900
2435701-01-2014130900
2435701-01-2014140900
2435701-01-2014150900
2435701-01-2014160900
2435701-01-2014170900
2435701-01-2014180900
2435701-01-2014190900
2435701-01-20142020002000
2435701-01-20142102000
2435701-01-20142202000
2435701-01-20142302000
2435701-01-20142402000

Thanks

This post has been answered by Frank Kulash on Apr 24 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 22 2014
Added on Apr 24 2014
2 comments
2,188 views