I have a situation where I am trying to determine the taxability of an invoiced line. If the invoiced line quantity is 6, for example, the detail lines should not exceed 6.
The problem is that if one of the detailed lines causes the cumulative quantity to exceed the invoiced quantity, then the query result should display the incremental difference between the invoice quantity and the cumulative quantity.
create table tt (
ln int,
pl int,
id int,
qty int,
tqty int,
tax varchar2(15),
td date
);
Insert into TT
(LN, PL, ID, QTY, TQTY,
TAX, td)
Values
(1, 9, 23149, 6, 1,
'Non-Taxable', TO_DATE('1/8/2018', 'MM/DD/YYYY'));
Insert into TT
(LN, PL, ID, QTY, TQTY,
TAX, td)
Values
(1, 9, 23149, 6, 2,
'Taxable', TO_DATE('11/15/2017', 'MM/DD/YYYY'));
Insert into TT
(LN, PL, ID, QTY, TQTY,
TAX, td)
Values
(1, 9, 23149, 6, 1,
'Taxable', TO_DATE('10/16/2017', 'MM/DD/YYYY'));
Insert into TT
(LN, PL, ID, QTY, TQTY,
TAX, td)
Values
(1, 9, 23149, 6, 1,
'Non-Taxable', TO_DATE('9/16/2017', 'MM/DD/YYYY'));
Insert into TT
(LN, PL, ID, QTY, TQTY,
TAX, td)
Values
(1, 9, 23149, 6, 2,
'Taxable', TO_DATE('5/2/2017', 'MM/DD/YYYY'));
COMMIT;SELECT *
FROM (SELECT ln,
pl,
id,
qty,
tqty,
tax,
td
,SUM(tqty) over (partition by pl ORDER BY td DESC) as CUMULATIVE_QTY
FROM tt
)
WHERE 1=1 --cumulative_qty <= qty
ORDER BY ln, td DESC;Here's what the data looks like:
LN PL ID QTY TQTY TAX TD CUMULATIVE_QTY
1 9 23149 6 1 Non-Taxable 1/8/2018 1
1 9 23149 6 2 Taxable 11/15/2017 3
1 9 23149 6 1 Taxable 10/16/2017 4
1 9 23149 6 1 Non-Taxable 9/16/2017 5
1 9 23149 6 2 Taxable 5/2/2017 7
If I un-comment the "cumulative_qty <= qty" clause, only the first 4 rows will display (since the cumulative quantity of 7 exceeds the invoiced quantity of 6), and when I eventually use PIVOT, it will display a qty of 6 of which, 3 are Taxable and 2 are Non-Taxable. This means that the quantity of 1 (which is Taxable) will be left off the query results.
Question:How do I write this query so the results look like this?
LN PL ID QTY TQTY TAX TD CUMULATIVE_QTY
1 9 23149 6 1 Non-Taxable 1/8/2018 1
1 9 23149 6 2 Taxable 11/15/2017 3
1 9 23149 6 1 Taxable 10/16/2017 4
1 9 23149 6 1 Non-Taxable 9/16/2017 5
1 9 23149 6 1 Taxable 5/2/2017 6
Here's one way to approach it:
Return all the rows where the cumulative total for the
previous row is less than the overall quantity. If the previous sum was greater than the overall total, you want to exclude the current row.
You can get the cumulative total for the prior row with the following windowing clause:
rows between unbounded preceding and 1 preceding
This will return null for the first row (it has no previous). So you'll need to map this to zero.
You can then compare this against the total. And return the least of the current running total and overall quantity in your final select:
select s.*, least (cumulative_qty, qty) cumulative
from (
select ln,
pl,
id,
qty,
tqty,
tax,
td,
sum(tqty) over (partition by pl order by td desc) as cumulative_qty,
nvl(sum(tqty) over (partition by pl order by td desc
rows between unbounded preceding and 1 preceding
), 0) as cumulative_qty_minus_1
from tt
) s
where cumulative_qty_minus_1 <= qty
order by ln, td desc;
LN PL ID QTY TQTY TAX TD CUMULATIVE_QTY CUMULATIVE_QTY_MINUS_1 CUMULATIVE
1 9 23149 6 1 Non-Taxable 2018 Jan 08 1 0 1
1 9 23149 6 2 Taxable 2017 Nov 15 3 1 3
1 9 23149 6 1 Taxable 2017 Oct 16 4 3 4
1 9 23149 6 1 Non-Taxable 2017 Sep 16 5 4 5
1 9 23149 6 2 Taxable 2017 May 02 7 5 6