Below are my SQL Statements :
WITH T1
AS (SELECT 'i1' ITEM,
'L1' LOC,
SYSDATE - 7 DAT,
0 QTY
FROM DUAL
UNION ALL
SELECT 'i1' ITEM,
'L1' LOC,
SYSDATE DAT,
100 QTY
FROM DUAL
UNION ALL
SELECT 'i1' ITEM,
'L1' LOC,
SYSDATE + 7 DAT,
0 QTY
FROM DUAL
UNION ALL
SELECT 'i1' ITEM,
'L1' LOC,
SYSDATE + 14 DAT,
20 QTY
FROM DUAL
UNION ALL
SELECT 'i1' ITEM,
'L1' LOC,
SYSDATE + 21 DAT,
10 QTY
FROM DUAL
UNION ALL
SELECT 'i1' ITEM,
'L1' LOC,
SYSDATE + 28 DAT,
0 QTY
FROM DUAL
UNION ALL
SELECT 'i2' ITEM,
'L2' LOC,
SYSDATE DAT,
0 QTY
FROM DUAL
UNION ALL
SELECT 'i2' ITEM,
'L2' LOC,
SYSDATE + 28 DAT,
0 QTY
FROM DUAL
UNION ALL
SELECT 'i3' ITEM,
'L3' LOC,
SYSDATE DAT,
10 QTY
FROM DUAL
UNION ALL
SELECT 'i3' ITEM,
'L3' LOC,
SYSDATE + 28 DAT,
0 QTY
FROM DUAL
UNION ALL
SELECT 'i3' ITEM,
'L3' LOC,
SYSDATE DAT,
230 QTY
FROM DUAL)
SELECT *
FROM t1;
Present Output
Expected Output:
Logic :
For a particular item and location order by date (ascending) first non - zero qty column should be filled with zero qty column
i.e.,
For Item i1 and Loc l1 we have 6 records and on 07th Jan, 21st Jan and 11th Feb we have zero quantities and 14th Jan, 28th Jan and 04th Feb are having non - zero quantities, when all these are order by date, quantity of 14th Jan should be derived to 07th Jan, quantity of 28th Jan should be derived to 21st Jan. and 11th Feb is having zero quantity but there is no record present after that so it should remain as it is.
Similar logic for all other combinations
What I have tried : I tried to achieve using first_value analytic function which is not fitting to my output
Thanks for your help in advance