I have data set like below:
| ELEMENT_KEY | Day | Hour | Volume |
| 24357 | 01-01-2014 | 1 | 0 |
| 24357 | 01-01-2014 | 2 | 200 |
| 24357 | 01-01-2014 | 3 | 0 |
| 24357 | 01-01-2014 | 4 | 0 |
| 24357 | 01-01-2014 | 5 | 0 |
| 24357 | 01-01-2014 | 6 | 0 |
| 24357 | 01-01-2014 | 7 | 0 |
| 24357 | 01-01-2014 | 8 | 0 |
| 24357 | 01-01-2014 | 9 | 900 |
| 24357 | 01-01-2014 | 10 | 0 |
| 24357 | 01-01-2014 | 11 | 0 |
| 24357 | 01-01-2014 | 12 | 0 |
| 24357 | 01-01-2014 | 13 | 0 |
| 24357 | 01-01-2014 | 14 | 0 |
| 24357 | 01-01-2014 | 15 | 0 |
| 24357 | 01-01-2014 | 16 | 0 |
| 24357 | 01-01-2014 | 17 | 0 |
| 24357 | 01-01-2014 | 18 | 0 |
| 24357 | 01-01-2014 | 19 | 0 |
| 24357 | 01-01-2014 | 20 | 2000 |
| 24357 | 01-01-2014 | 21 | 0 |
| 24357 | 01-01-2014 | 22 | 0 |
| 24357 | 01-01-2014 | 23 | 0 |
| 24357 | 01-01-2014 | 24 | 0 |
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_KEY | Day | Hour | Volume | previous_non_zero_volume |
| 24357 | 01-01-2014 | 1 | 0 | 0 |
| 24357 | 01-01-2014 | 2 | 200 | 200 |
| 24357 | 01-01-2014 | 3 | 0 | 200 |
| 24357 | 01-01-2014 | 4 | 0 | 200 |
| 24357 | 01-01-2014 | 5 | 0 | 200 |
| 24357 | 01-01-2014 | 6 | 0 | 200 |
| 24357 | 01-01-2014 | 7 | 0 | 200 |
| 24357 | 01-01-2014 | 8 | 0 | 200 |
| 24357 | 01-01-2014 | 9 | 900 | 900 |
| 24357 | 01-01-2014 | 10 | 0 | 900 |
| 24357 | 01-01-2014 | 11 | 0 | 900 |
| 24357 | 01-01-2014 | 12 | 0 | 900 |
| 24357 | 01-01-2014 | 13 | 0 | 900 |
| 24357 | 01-01-2014 | 14 | 0 | 900 |
| 24357 | 01-01-2014 | 15 | 0 | 900 |
| 24357 | 01-01-2014 | 16 | 0 | 900 |
| 24357 | 01-01-2014 | 17 | 0 | 900 |
| 24357 | 01-01-2014 | 18 | 0 | 900 |
| 24357 | 01-01-2014 | 19 | 0 | 900 |
| 24357 | 01-01-2014 | 20 | 2000 | 2000 |
| 24357 | 01-01-2014 | 21 | 0 | 2000 |
| 24357 | 01-01-2014 | 22 | 0 | 2000 |
| 24357 | 01-01-2014 | 23 | 0 | 2000 |
| 24357 | 01-01-2014 | 24 | 0 | 2000 |
Thanks