Subtraction value with same table

1
votes

I'm stuck with my query using Oracle, please help me.

This is my table:

id cust order qty value
0001 abc 10.000 2000 8000
0001 abc 10.000 4000 6000
0001 abc 10.000 4000 6000
0002 bcd 5000 2000 3000
0003 fgh 3000 1000 2000
0004 ghj 5000 NULL 5000

result that i want :

id cust order qty value
0001 abc 10.000 2000 8000
0001 abc 10.000 4000 4000
0001 abc 10.000 4000 0
0002 bcd 5000 2000 3000
0003 fgh 3000 1000 2000
0004 ghj 5000 NULL 5000

value = order - qty

So, the calculation based on same id and same cust, they are subtract from rows before, if id and cust does not same, they subtract normally.

Thanks in advance

3
sqloracle
For a given id and cust, how do you decide which row is "first" and which is "second" etc.? In your example, you have two rows, with qty 2000 and 4000. What tells you (more importantly, what tells me, and what tells Oracle) that the row with qty of 2000 should be first, and the row with 4000 should be second, and not the other way around? Other than that, the problem seems trivial (using analytic sum()). – mathguy
You should at the very least also include what the output table looks like here. – Tim Biegeleisen
@TimBiegeleisen - I believe what he showed is the output. The input must have null in the last column (or the value column doesn't even exist in the table; it shouldn't exist there). – mathguy
Voting to close as unclear, and most users won't have your insight to figure this out (I know I certainly didn't). – Tim Biegeleisen

3 Answers

0
votes

You can try the below - using lag()

select id,cust,orders,qty,
orders-qty-coalesce(lag(qty) over(partition by id,cust order by qty),0) as value
from tablename

OUTPUT:

id          cust        orders  qty    value
0001        abc         10000   2000    8000
0001        abc         10000   4000    4000
0002        bcd         5000    2000    3000
0003        fgh         3000    1000    2000
0
votes

I am going to assume that you have another column which tells you the order in which the qty should be subtracted from the order column and have called this the datetime column (but you can use whatever column you have available to provide a deterministic ordering of the rows). Given that, you can calculate the value using an analytic SUM function:

SELECT id,
       cust,
       datetime,
       order_total,
       qty,
       order_total
         - SUM( COALESCE( qty, 0 ) )
             OVER ( PARTITION BY id, cust, order_total ORDER BY datetime )
         AS value
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( id, cust, datetime, order_total, qty ) AS
SELECT '0001',  'abc',  DATE '2021-01-01', 10000,   2000 FROM DUAL UNION ALL
SELECT '0001',  'abc',  DATE '2021-01-02', 10000,   4000 FROM DUAL UNION ALL
SELECT '0001',  'abc',  DATE '2021-01-03', 10000,   4000 FROM DUAL UNION ALL
SELECT '0002',  'bcd',  DATE '2021-01-01', 5000,    2000 FROM DUAL UNION ALL
SELECT '0003',  'fgh',  DATE '2021-01-01', 3000,    1000 FROM DUAL UNION ALL
SELECT '0004',  'ghj',  DATE '2021-01-01', 5000,    NULL FROM DUAL;

Outputs:

ID   | CUST | DATETIME  | ORDER_TOTAL |  QTY | VALUE
:--- | :--- | :-------- | ----------: | ---: | ----:
0001 | abc  | 01-JAN-21 |       10000 | 2000 |  8000
0001 | abc  | 02-JAN-21 |       10000 | 4000 |  4000
0001 | abc  | 03-JAN-21 |       10000 | 4000 |     0
0002 | bcd  | 01-JAN-21 |        5000 | 2000 |  3000
0003 | fgh  | 01-JAN-21 |        3000 | 1000 |  2000
0004 | ghj  | 01-JAN-21 |        5000 | null |  5000

db<>fiddle here

0
votes

Seems like you are trying to partition by id and order by id and qty. You can try

Select id,  cust,   order,  qty,  order-coalesce(sum(qty) over (partition by id order by id,  qty),0) as value from myTable;