Home » SQL & PL/SQL » SQL & PL/SQL » Quantity allocation to discount slabs (Oracle 11.2.0.1.0)
| Quantity allocation to discount slabs [message #658565] |
Mon, 19 December 2016 22:37  |
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
|
Senior Member |
|
|
Dear Friends,
Here I've a requirement, wherein i want to allocate quantity accross the available discount slabs.
CREATE TABLE DSP.TEMP1
(
PID NUMBER(8),
QUANTITY NUMBER(12,3),
PRICE NUMBER(12,3)
)
/
Insert into TEMP1 (PID, QUANTITY, PRICE) Values (1, 100, 125.5);
Insert into TEMP1 (PID, QUANTITY, PRICE) Values (2, 225, 90);
Insert into TEMP1 (PID, QUANTITY, PRICE) Values (3, 90, 140);
Insert into TEMP1 (PID, QUANTITY, PRICE) Values (4, 107, 20);
Insert into TEMP1 (PID, QUANTITY, PRICE) Values (5, 175, 20.5);
CREATE TABLE DSP.TEMP2
(
ID NUMBER(8),
FROM_QUANTITY NUMBER(12,3),
TO_QUANTITY NUMBER(12,3),
DISCOUNT NUMBER(12,3)
)
/
Insert into TEMP2 (ID, FROM_QUANTITY, TO_QUANTITY, DISCOUNT) Values (1, 0, 750, 0.5);
Insert into TEMP2 (ID, FROM_QUANTITY, TO_QUANTITY, DISCOUNT) Values (2, 751, 1000, 0.25);
Insert into TEMP2 (ID, FROM_QUANTITY, TO_QUANTITY, DISCOUNT) Values (3, 1001, 1250, 0.1);
Allocation logic will as followed.
Previously sold (sold till now 640 quantity).
For PID:1 quantity 100 will be fall into ID:1 (640+100 = 740)
PID QUANTITY PRICE ID DISCOUNT
1 100 125.5 1 0.5
For PID:2 quantity 225 will be fall into to slabs
PID QUANTITY PRICE ID DISCOUNT
2 10 90 1 0.5
2 215 90 2 0.25
For PID:3 quantity 90 , above logic will be carry forwarded upto quantity finish.
PID QUANTITY PRICE ID DISCOUNT
3 35 140 2 0.25
3 55 140 3 0.1
For PID:4 quantity 107
PID QUANTITY PRICE ID DISCOUNT
4 107 20 3 0.1
For PID:5 quantity 75
PID QUANTITY PRICE ID DISCOUNT
5 88 20.5 3 0.1
5 87 20.5
Final Result will be like this,
PID QUANTITY PRICE ID DISCOUNT
1 100 125.5 1 0.5
2 10 90 1 0.5
2 215 90 2 0.25
3 35 140 2 0.25
3 55 140 3 0.1
4 107 20 3 0.1
5 88 20.5 3 0.1
5 87 20.5
Please help me out, if this can be done via usage of SQL analytic function.
Thanks & Regards
Jimit
|
|
|
|
| Re: Quantity allocation to discount slabs [message #658568 is a reply to message #658565] |
Tue, 20 December 2016 01:17  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- test data:
SCOTT@orcl_12.1.0.2.0> select * from temp1 order by pid
2 /
PID QUANTITY PRICE
---------- ---------- ----------
1 100 125.5
2 225 90
3 90 140
4 107 20
5 175 20.5
5 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from temp2 order by id
2 /
ID FROM_QUANTITY TO_QUANTITY DISCOUNT
---------- ------------- ----------- ----------
1 0 750 .5
2 751 1000 .25
3 1001 1250 .1
3 rows selected.
-- query:
SCOTT@orcl_12.1.0.2.0> select nvl (pid, lead (pid ignore nulls) over (order by qty)) pid,
2 qty - nvl (lag (qty) over (order by qty), 0) qty,
3 nvl (price, lead (price ignore nulls) over (order by qty)) price,
4 nvl (id, lead (id ignore nulls) over (order by qty)) id,
5 nvl (discount, lead (discount ignore nulls) over (order by qty)) discount
6 from (select pid, sum(quantity) over (order by pid) qty, price, to_number(null) id, to_number(null) discount
7 from temp1
8 union all
9 select null pid, to_quantity-640 qty, null price, id, discount
10 from temp2
11 order by qty)
12 /
PID QTY PRICE ID DISCOUNT
---------- ---------- ---------- ---------- ----------
1 100 125.5 1 .5
2 10 90 1 .5
2 215 90 2 .25
3 35 140 2 .25
3 55 140 3 .1
4 107 20 3 .1
5 88 20.5 3 .1
5 87 20.5
8 rows selected.
|
|
|
|
| Re: Quantity allocation to discount slabs [message #658581 is a reply to message #658568] |
Tue, 20 December 2016 04:20 |
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
|
Senior Member |
|
|
Dear Barbara,
Thanks for the reply, but in some cases I'm not able get correct discount details / slabs. Here is the case.
Previously sold (sold till now 8400 quantity).
Insert into TEMP1 (PID, QUANTITY, PRICE) Values (1, 4050, 100);
Insert into TEMP2 (ID, FROM_QUANTITY, TO_QUANTITY, DISCOUNT) Values (1, 0, 5000, 0.5);
Insert into TEMP2 (ID, FROM_QUANTITY, TO_QUANTITY, DISCOUNT) Values (2, 5001, 7500, 0.25);
Insert into TEMP2 (ID, FROM_QUANTITY, TO_QUANTITY, DISCOUNT) Values (3, 7501, 10000, 0.1);
select nvl (pid, lead (pid ignore nulls) over (order by qty)) pid,
qty - nvl (lag (qty) over (order by qty), 0) qty,
nvl (price, lead (price ignore nulls) over (order by qty)) price,
nvl (id, lead (id ignore nulls) over (order by qty)) id,
nvl (discount, lead (discount ignore nulls) over (order by qty)) discount
from
(
select pid, sum(quantity) over (order by pid) qty, price, to_number(null) id, to_number(null) discount from temp1
union all
select null pid, to_quantity-8400 qty, null price, id, discount from temp2 order by qty
)
/
PID QTY PRICE ID DISCOUNT
---------- ---------- ---------- ---------- ----------
1 -3400 100 1 .5
1 2500 100 2 .25
1 2500 100 3 .1
1 2450 100
Result would be like follow.
PID QTY PRICE ID DISCOUNT
---------- ---------- ---------- ---------- ----------
1 1600 100 3 .1
1 2450 100
Thanks
Jimit
|
|
|
|
| Re: Quantity allocation to discount slabs [message #658593 is a reply to message #658581] |
Tue, 20 December 2016 16:04 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I have added a line to the query, as shown below.
-- test data:
SCOTT@orcl_12.1.0.2.0> select * from temp1 order by pid
2 /
PID QUANTITY PRICE
---------- ---------- ----------
1 4050 100
1 row selected.
SCOTT@orcl_12.1.0.2.0> select * from temp2 order by id
2 /
ID FROM_QUANTITY TO_QUANTITY DISCOUNT
---------- ------------- ----------- ----------
1 0 5000 .5
2 5001 7500 .25
3 7501 10000 .1
3 rows selected.
-- revised query with added line where indicated:
SCOTT@orcl_12.1.0.2.0> select nvl (pid, lead (pid ignore nulls) over (order by qty)) pid,
2 qty - nvl (lag (qty) over (order by qty), 0) qty,
3 nvl (price, lead (price ignore nulls) over (order by qty)) price,
4 nvl (id, lead (id ignore nulls) over (order by qty)) id,
5 nvl (discount, lead (discount ignore nulls) over (order by qty)) discount
6 from (select pid, sum(quantity) over (order by pid) qty, price, to_number(null) id, to_number(null) discount
7 from temp1
8 union all
9 select null pid, to_quantity-8400 qty, null price, id, discount
10 from temp2
11 -- add the line below:
12 where to_quantity-8400 > 0
13 order by qty)
14 /
PID QTY PRICE ID DISCOUNT
---------- ---------- ---------- ---------- ----------
1 1600 100 3 .1
1 2450 100
2 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 04:26:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
