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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8884
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8884
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.
Re: Quantity allocation to discount slabs [message #658687 is a reply to message #658593] Fri, 23 December 2016 03:07 Go to previous message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Barbara,

It has worked perfectly to resolve my query. Thanks again.

Regards

Jimit
Previous Topic: transforming rows to columns
Next Topic: DB-LINK to mysql
Goto Forum:
  


Current Time: Tue Oct 16 01:04:08 CDT 2018