Hi,
I have to calculate accumulated value for a column, but I have to filter some rows (and it depends on that accumulated value).
For example:
CREATE TABLE dummy_table
(
var_id NUMBER(5) PRIMARY KEY,
prc number(5),
qty number(5),
v_type varchar2(1)
);
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY) Values(6, 15, 40);
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY, V_TYPE) Values (5, 14, 35, 'A');
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY) Values (4, 13, 30);
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY, V_TYPE) Values (3, 12, 25, 'A');
Insert into US_GAS.DUMMY_TABLE(VAR_ID, PRC, QTY) Values (2, 11, 20);
Insert into US_GAS.DUMMY_TABLE (VAR_ID, PRC, QTY) Values (1, 10, 15);
COMMIT;
select prc, qty, v_type,
SUM (qty) OVER (/*PARTITION BY prc, var_id*/ ORDER BY prc desc, var_id desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) accQty
from dummy_table
order by prc desc
With the query you get this:
------------------------------------------
PRC QTY V_TYPE ACCQTY
15 40 40
14 35 A 75
13 30 105
12 25 A 130
11 20 150
10 15 165
------------------------------------------
Now, based on that accumulated value (and the value of V_TYPE), you have to discard the row of PRC = 12 (for example). The output should be this one:
------------------------------------------
PRC QTY V_TYPE ACCQTY
15 40 40
14 35 A 75
13 30 105
11 20
12510 15
140------------------------------------------
That is, you need to calculate the acumulated value first, and if you have to discard the row (one or more rows could be discarded), the acumulated value of the rest of rows should be update dinamically.
Can this be done in a select statement? Do I need a function or a procedure?
Thanks
-------------------------------------------------------------------------
Our request for more information:
Thanks for providing a test case. But I don't understand what the rule is. Could you clarify please? Specifically, why do you discard PRC = 12? What's the formula that tells us we can reject it? How do I know which future rows to discard? Please update this question with this information, Thanks,
-------------------------------------------------------------------------
Of course, I will add more information, based on the same data:
------------------------------------------
PRC QTY V_TYPE (ACCQTY)
15 40 40
14 35 A 75
13 30 105
12 25 A 130
11 20 150
10 15 165
------------------------------------------
Where:
PRC : Price
QTY : Quantity
V_TYPE : Can be “null”, or “A”, where “A” means that you can take all the quantity or nothing (All or nothing). Null means that you don’t need to take all Qty.
ACCQty : accumulative quantity
Now you have to take some maxium value of quantity. For example, you only can take 125 (accumulative), and you can’t divide the registers of type “A”. So you have to discard the row with price 12 (because with it you have 130). So I need a select that returns me:
------------------------------------------
PRC QTY V_TYPE ACCQTY
15 40 40
14 35 A 75
13 30 105
11 15 125------------------------------------------
In the last row (price 11), the Qty sould be put at 15 because you only have 15 left. Can this be done with a select?
Thanks a lot again
I still don't really understand what the rule is. Why are you excluding PRC = 12 but not PRC = 14? If there's a PRC = 10 of type A, what happens?
Anyway, if you want to exclude them from the running total, you just need to find a way to map quantities to null.
For example:
drop table dummy_table purge;
CREATE TABLE dummy_table
(
var_id NUMBER(5) PRIMARY KEY,
prc number(5),
qty number(5),
v_type varchar2(1)
);
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY) Values(6, 15, 40);
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY, V_TYPE) Values (5, 14, 35, 'A');
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY) Values (4, 13, 30);
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY, V_TYPE) Values (3, 12, 25, 'A');
Insert into DUMMY_TABLE(VAR_ID, PRC, QTY) Values (2, 11, 20);
Insert into DUMMY_TABLE (VAR_ID, PRC, QTY) Values (1, 10, 15);
COMMIT;
select prc, qty, v_type,
sum(
case
when v_type is null or
(v_type = 'A' and rn = 1 ) then qty
end
) over (order by prc desc, rn) accqty
from (
select var_id, prc, qty, v_type,
row_number() over (partition by v_type order by prc desc, var_id desc) rn
from dummy_table
)
order by prc desc, var_id desc;
PRC QTY V_TYPE ACCQTY
15 40 40
14 35 A 75
13 30 105
12 25 A 105
11 20 125
10 15 140
If you want to remove the uncounted rows from the results, put the conditions from the case in the where clause:
select prc, qty, v_type,
sum(
case
when v_type is null or
(v_type = 'A' and rn = 1 ) then qty
end
) over (order by prc desc, rn) accqty
from (
select var_id, prc, qty, v_type,
row_number() over (partition by v_type order by prc desc, var_id desc) rn
from dummy_table
)
where v_type is null or
(v_type = 'A' and rn = 1 )
order by prc desc, var_id desc;
PRC QTY V_TYPE ACCQTY
15 40 40
14 35 A 75
13 30 105
11 20 125
10 15 140
I've guessed that you want to keep the first row with v_type = 'A', but not the others. If this is incorrect, just update the case clause so that it returns null when there's a value you want to skip.