Home » SQL & PL/SQL » SQL & PL/SQL » Calculating running percentage discount
Calculating running percentage discount Thu, 05 October 2006 05:58
 jaydave6 Messages: 4Registered: October 2006 Location: India Junior Member
Hi,

I need to calculate promotion in percentage (if % off given, keep the value as
it is, if amount off given, convert it to percentage) per customer and item.

I have following table:

CREATE TABLE TEMP_PROMO_CALC_2
(
CTRY_CODE CHAR(3 BYTE) NOT NULL,
CO_CODE CHAR(3 BYTE) NOT NULL,
CUST_NBR NUMBER NOT NULL,
ITEM_ID CHAR(20 BYTE),
EVENT_NBR NUMBER NOT NULL,
PROMO_SEQ_NBR NUMBER(2),
EVENT_ALLOW_PCT NUMBER(5,4),
EVENT_ALLOW_AMT NUMBER,
BASE_PRICE NUMBER,
NEW_PRICE NUMBER,
PERCENT_DIC NUMBER
)

Having following example data in this table:

INSERT INTO TEMP_PROMO_CALC_2 ( CTRY_CODE, CO_CODE, CUST_NBR, ITEM_ID,
EVENT_NBR, PROMO_SEQ_NBR,
EVENT_ALLOW_PCT, EVENT_ALLOW_AMT, BASE_PRICE, NEW_PRICE ) VALUES (
'138', '002', 1, '100', 1, 1, 4, 0, 100, NULL);

INSERT INTO TEMP_PROMO_CALC_2 ( CTRY_CODE, CO_CODE, CUST_NBR, ITEM_ID,
EVENT_NBR, PROMO_SEQ_NBR,
EVENT_ALLOW_PCT, EVENT_ALLOW_AMT, BASE_PRICE, NEW_PRICE ) VALUES (
'138', '002', 1, '100', 5, 2, 0, 20, 100, NULL);

COMMIT;

Here, EVENT_ALLOW_PCT is the % discount, while EVENT_ALLOW_AMT is the discount
amount in \$.

I want following things:
- First convert all the promotions in % form
- Apply the promotion in the seq. mentioned by PROMO_SEQ_NBR

In above example,

- if 4% (Event Seq 1, so this will be applied first) given on a 100 \$
price, NEW_PRICE field value after applying this promotion will be 96 \$. The
PERCENT_DIC field value will be 4% for PROMO_SEQ_NBR -> 1.

- Then 20\$ amount off promotion is given. This 20\$ amount off promotion
needs to be converted into % off i.e. 20\$ will be deducted from the 96 \$ (not from base price = 100). In this
example, the PERCENT_DIC for this 2nd seq. will be 20.83 % [calculated as
(20*100)/96] and NEW_PRICE filed value will be 76 \$

Desrired Result:
```CUST_NBR   ITEM_ID    EVENT_NBR     PROMO_SEQ_NBR   EVENT_ALLOW_PCT
---------  --------  ---------   -------------   ----------------
1           100           1                 1                  4
1           100           5                 2                  0

EVENT_ALLOW_AMT    BASE_PRICE  NEW_PRICE   PERCENT_DIC
----------------   ----------  ---------   -----------
0          100         96             4
20          100         76         20.83
```

How can this be achieved with the analytic functions?

Regards,

Jay
Re: Calculating running percentage discount [message #196555 is a reply to message #196431] Thu, 05 October 2006 21:47
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Senior Member
With great difficulty. I'm not saying its impossible, because there are some terribly clever people out there.

What you are talking about is deriving a particular column based on the value of the SAME column in a neighboring row. Analytic functions don't work like that; they allow you to calculate a column based on the values of DIFFERENT columns from neighboring rows.

I've seen this style of problem before and never encountered a full solution using Analytic Functions.

The two solutions that come to mind are:
• Use Data Cartridge to build your own aggregate function. Search on CONCAT_ALL in this forum for an example (not a helpful one though) of a Data Cartridge. I am not completely sure that it could be done. If it could, the cartridge code would be ugly, but the resulting SQL would look quite elegant.
• Use a PL/SQL Pipelined Function (search the PL/SQL guide). This can give you the capability to stream data from SQL to PL/SQL and back to SQL again (I'm over-simplifying it, but you get the idea), or more simply, to stream the output of a PL/SQL function into a SQL. This could definitely be done.

Ross Leishman
Re: Calculating running percentage discount [message #196623 is a reply to message #196431] Fri, 06 October 2006 04:59
 jaydave6 Messages: 4Registered: October 2006 Location: India Junior Member

Lets see what other experts comment on this!!
Re: Calculating running percentage discount [message #196732 is a reply to message #196623] Fri, 06 October 2006 22:16
 Barbara Boehmer Messages: 8709Registered: November 2002 Location: California, USA Senior Member

```SCOTT@10gXE> SELECT promo_seq_nbr, event_allow_pct, event_allow_amt, base_price,new_price, percent_dic
2  FROM   temp_promo_calc_2
3  /

PROMO_SEQ_NBR EVENT_ALLOW_PCT EVENT_ALLOW_AMT BASE_PRICE  NEW_PRICE PERCENT_DIC
------------- --------------- --------------- ---------- ---------- -----------
1               4               0        100
2               0              20        100

SCOTT@10gXE> UPDATE temp_promo_calc_2 t1
2  SET    (t1.new_price, t1.percent_dic) =
3  	    (SELECT t2.new_price, t2.percent_dic
4  	     FROM   (SELECT promo_seq_nbr, event_allow_amt, new_price,
5  			    ROUND ((event_allow_amt * 100) /
6  			      NVL (LAG (new_price) OVER (ORDER BY promo_seq_nbr), base_price), 2)
7  			      AS percent_dic
8  		     FROM   (SELECT promo_seq_nbr, event_allow_amt, base_price,
9  				    base_price -
10  				      SUM (event_allow_amt) OVER
11  					(ORDER BY promo_seq_nbr) AS new_price
12  			     FROM   (SELECT promo_seq_nbr,
13  					    DECODE (event_allow_amt,
14  						    0, (event_allow_pct / 100) * base_price,
15  						    event_allow_amt) AS event_allow_amt,
16  					    base_price
17  				     FROM   temp_promo_calc_2))) t2
18  	     WHERE  t1.promo_seq_nbr = t2.promo_seq_nbr)
19  /

2 rows updated.

SCOTT@10gXE> SELECT promo_seq_nbr, event_allow_pct, event_allow_amt, base_price,new_price, percent_dic
2  FROM   temp_promo_calc_2
3  /

PROMO_SEQ_NBR EVENT_ALLOW_PCT EVENT_ALLOW_AMT BASE_PRICE  NEW_PRICE PERCENT_DIC
------------- --------------- --------------- ---------- ---------- -----------
1               4               0        100         96           4
2               0              20        100         76       20.83

SCOTT@10gXE>

```

Re: Calculating running percentage discount [message #196746 is a reply to message #196732] Sat, 07 October 2006 00:12
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Senior Member
Bravo.

Trying to get my head around it. What would happen if there was another % discount at event 3, say 10%.
Our running total from event 2 is \$76, so a 10% discount on that would be \$7.60, leaving \$68.40.

Barbara, would your SQL give the 3rd discount as \$7.60, or would it make it \$10 (being 10% of the original \$100) making the final total \$66 instead of \$68.40?

Ross Leishman
Re: Calculating running percentage discount [message #196766 is a reply to message #196746] Sat, 07 October 2006 02:37
 Barbara Boehmer Messages: 8709Registered: November 2002 Location: California, USA Senior Member
It would end up \$66 instead of \$68.40. There was insufficient data in the two-row sample to determine what the original poster wanted. If it needs to be \$68.40, then I believe I would give up on the single update statement and settle for some pl/sql looping through a cursor:

```SCOTT@10gXE> SELECT promo_seq_nbr, event_allow_pct, event_allow_amt, base_price,new_price, percent_dic
2  FROM   temp_promo_calc_2
3  /

PROMO_SEQ_NBR EVENT_ALLOW_PCT EVENT_ALLOW_AMT BASE_PRICE  NEW_PRICE PERCENT_DIC
------------- --------------- --------------- ---------- ---------- -----------
1               4               0        100
2               0              20        100
3              10               0        100

SCOTT@10gXE> DECLARE
2    v_new_price	 NUMBER;
3    v_percent_dic	 NUMBER;
4  BEGIN
5    FOR r IN (SELECT * FROM temp_promo_calc_2 ORDER BY promo_seq_nbr) LOOP
6  	 IF v_new_price IS NULL THEN
7  	   v_new_price := r.base_price;
8  	 END IF;
9  	 IF r.event_allow_amt > 0 THEN
10  	   v_percent_dic := ROUND (r.event_allow_amt * 100 / v_new_price, 2);
11  	   v_new_price := v_new_price - r.event_allow_amt;
12  	 ELSE
13  	   v_percent_dic := r.event_allow_pct;
14  	   v_new_price := v_new_price - (r.event_allow_pct * v_new_price / 100 );
15  	 END IF;
16  	 UPDATE temp_promo_calc_2
17  	 SET	new_price = v_new_price,
18  		percent_dic = v_percent_dic
19  	 WHERE	promo_seq_nbr = r.promo_seq_nbr;
20    END LOOP;
21  END;
22  /

PL/SQL procedure successfully completed.

SCOTT@10gXE> SELECT promo_seq_nbr, event_allow_pct, event_allow_amt, base_price, new_price, percent_dic
2  FROM   temp_promo_calc_2
3  /

PROMO_SEQ_NBR EVENT_ALLOW_PCT EVENT_ALLOW_AMT BASE_PRICE  NEW_PRICE PERCENT_DIC
------------- --------------- --------------- ---------- ---------- -----------
1               4               0        100         96           4
2               0              20        100         76       20.83
3              10               0        100       68.4          10

SCOTT@10gXE>
```

Re: Calculating running percentage discount [message #197643 is a reply to message #196431] Thu, 12 October 2006 02:28
 jaydave6 Messages: 4Registered: October 2006 Location: India Junior Member
That's Decent.

Actually, I was trying to get it done without using procedural code.

I have written followning code. Please feel free to give any comments.

```
SELECT   ctry_code, co_code, cust_nbr, item_id,
DECODE (r,
1, TO_CHAR (event_1),
2, event_2,
3, event_3,
4, event_4,
5, event_5,
6, event_6
) event_deduction
FROM (SELECT ctry_code, co_code, cust_nbr, item_id, event_no_1, event_1,
((100 * (base_price - event_1)) / base_price) event_1_p,
event_no_2, event_2,
((event_1 * (event_1 - event_2)) / event_1) event_2_p,
event_no_3, event_3,
((event_2 * (event_2 - event_3)) / event_2) event_3_p,
event_no_4, event_4,
((event_3 * (event_3 - event_4)) / event_3) event_4_p,
event_no_5, event_5,
((event_4 * (event_4 - event_5)) / event_4) event_5_p,
event_no_6, event_6,
((event_5 * (event_5 - event_6)) / event_5) event_6_p,
base_price, last_sell_price, s, t, u, v, w, x, tot_disc
FROM (SELECT ctry_code, co_code, cust_nbr, item_id,
event_allow_pct, event_allow_amt, event_no_1,
event_no_2, event_no_3, event_no_4, event_no_5,
event_no_6, base_price, s, t, u, v, w, x, y1, y2, y3,
y4, y6, y6 last_sell_price,
CASE
WHEN s <> t
THEN s + t - base_price
ELSE (s + t) / 2
END event_1,
CASE
WHEN u <> v
THEN u + v - t
ELSE (u + v) / 2
END event_2,
CASE
WHEN w <> x
THEN w + x - v
ELSE (w + x) / 2
END event_3,
CASE
WHEN y1 <> y2
THEN y1 + y2 - x
ELSE (y1 + y2) / 2
END event_4,
CASE
WHEN y3 <> y4
THEN y3 + y4 - x
ELSE (y3 + y4) / 2
END event_5,
CASE
WHEN y5 <> y6
THEN y5 + y6 - x
ELSE (y5 + y6) / 2
END event_6,
100 - (100 * y6) / base_price tot_disc
FROM (SELECT m.*, ROUND (y5, 4) - NVL (pivot6amt, 0) y6
FROM (SELECT l.*,
ROUND (y4, 4)
-   ROUND (y4, 4)
* NVL (pivot6_6pct, 0)
/ 100 y5
FROM (SELECT k.*,
ROUND (y3, 4)
- NVL (pivot5amt, 0) y4
FROM (SELECT j.*,
ROUND (y2, 4)
-   ROUND (y2, 4)
* NVL (pivot5_5pct,
0
)
/ 100 y3
FROM (SELECT i.*,
ROUND
(y1,
4
)
- NVL
(pivot4amt,
0
) y2
FROM (SELECT h.*,
ROUND
(x,
4
)
-   ROUND
(x,
4
)
* NVL
(pivot4_4pct,
0
)
/ 100
y1
FROM (SELECT f.*,
ROUND
(w,
4
)
-   ROUND
(w,
4
)
* NVL
(pivot3_3pct,
0
)
/ 100
x
FROM (SELECT e.*,
ROUND
(v,
4
)
- NVL
(pivot3amt,
0
)
w
FROM (SELECT d.*,
ROUND
(u,
4
)
- NVL
(pivot2_2amt,
0
)
v
FROM (SELECT c.*,
ROUND
(t,
4
)
-   ROUND
(t,
4
)
* NVL
(pivot2pct,
0
)
/ 100
u
FROM (SELECT b.*,
ROUND
(s,
4
)
-   ROUND
(s,
4
)
* NVL
(pivot1_1pct,
0
)
/ 100
t
FROM (SELECT a.*,
base_price
- NVL
(pivot1amt,
0
)
s
FROM (SELECT   ctry_code,
co_code,
cust_nbr,
item_id,
event_allow_pct,
event_allow_amt,
base_price,
MAX
(DECODE
(rn,
1, event_nbr
)
)
event_no_1,
MAX
(DECODE
(rn,
1, event_allow_amt
)
)
pivot1amt,
MAX
(DECODE
(rn,
1, event_allow_pct
)
)
pivot1_1pct,
MAX
(DECODE
(rn,
2, event_nbr
)
)
event_no_2,
MAX
(DECODE
(rn,
2, event_allow_pct
)
)
pivot2pct,
MAX
(DECODE
(rn,
2, event_allow_amt
)
)
pivot2_2amt,
MAX
(DECODE
(rn,
3, event_nbr
)
)
event_no_3,
MAX
(DECODE
(rn,
3, event_allow_amt
)
)
pivot3amt,
MAX
(DECODE
(rn,
3, event_allow_pct
)
)
pivot3_3pct,
MAX
(DECODE
(rn,
4, event_nbr
)
)
event_no_4,
MAX
(DECODE
(rn,
4, event_allow_amt
)
)
pivot4amt,
MAX
(DECODE
(rn,
4, event_allow_pct
)
)
pivot4_4pct,
MAX
(DECODE
(rn,
5, event_nbr
)
)
event_no_5,
MAX
(DECODE
(rn,
5, event_allow_amt
)
)
pivot5amt,
MAX
(DECODE
(rn,
5, event_allow_pct
)
)
pivot5_5pct,
MAX
(DECODE
(rn,
6, event_nbr
)
)
event_no_6,
MAX
(DECODE
(rn,
6, event_allow_amt
)
)
pivot6amt,
MAX
(DECODE
(rn,
6, event_allow_pct
)
)
pivot6_6pct
FROM (SELECT ctry_code,
co_code,
cust_nbr,
item_id,
event_nbr,
base_price,
event_allow_amt,
event_allow_pct,
ROW_NUMBER
() OVER (PARTITION BY ctry_code, co_code, cust_nbr, item_id ORDER BY promo_seq_nbr)
rn
FROM temp_promo_calc_2
WHERE base_price <>
0)
WHERE rn <=
6
GROUP BY ctry_code,
co_code,
cust_nbr,
item_id,
base_price) a) b) c) d) e) f) h) i) j) k) l) m) g)),
(SELECT   ROWNUM r
FROM temp_promo_calc_2
WHERE ROWNUM <= 6
ORDER BY ctry_code, co_code, cust_nbr, item_id)
ORDER BY ctry_code, co_code, cust_nbr, item_id, ROWNUM;

And this results into,

CTR CO_   CUST_NBR ITEM_ID              EVENT_DEDUCTION
--- --- ---------- -------------------- -------------------
138 002          1 100                  96
138 002          1 100                  76
138 002          1 100                  68.4

```

Regards,
Jay

Re: Calculating running percentage discount [message #197646 is a reply to message #197643] Thu, 12 October 2006 03:08
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Senior Member
Hey that's fantastic. You've just guaranteed yourself a job for life. They can't get rid of you now, because there's not another soul in the world who could possibly tell what the heck the SQL is trying to do. But now you can never take holidays either, so that's not so good.

Scenaro: The SQL is producing a result that is unexpected by the user. You are on leave, so your manager assigns it to one of your colleages to debug. What will they do:
1. Dilligently spend the next week working through the code nested query by nested query until they understand better than you ever did in order to debug it.
2. Post it on OraFAQ Experts Forum and ask for help.
3. Attempt both of the above, fail, and then check themselves into a mental health institution.

All sarcasm aside (I got carried away), SQL is good for some things (finding, joining, and summarizing data), and bad at some things (conditional processing, iteration, recursion). PL/SQL is the opposite.

Use the tool that fits the job.

Ross Leishman
Re: Calculating running percentage discount [message #197649 is a reply to message #197646] Thu, 12 October 2006 03:17
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Senior Member
Another anecdote.

I work for a consulting firm. Several years ago, a colleague wrote a SQL to process data that was not entirely unlike the one above. Like yours, the requirement was fairly simple, but it required iteration AND the data quality was imperfect so it required conditional processing.

The SQL grew and grew.

Six months later, the project had spent over twice its allocated budget and the job still didn't work (about 10% error rate). The client - worth over \$5M p/a to my firm - threatend to close our contract.

They sent me. I spent a week analyzing the requirements, discarded ALL of the existing code, and re-wrote it in PL/SQL in less than 2 days. The project was closed (successfully) a week later.

That client is now worth over \$10M p/a to our firm (and that colleague works elsewhere).

Hope that helps you decide.

Ross Leishman
Re: Calculating running percentage discount [message #197654 is a reply to message #197649] Thu, 12 October 2006 03:40
 Frank Messages: 7880Registered: March 2000 Senior Member
Lesson learned:
Leave your sql as-is. Someone else WILL fix it.

Seriously, good one Ross, it explains it better than a zillion warnings 'dont do this'
Re: Calculating running percentage discount [message #197661 is a reply to message #197654] Thu, 12 October 2006 03:54
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Senior Member
 Frank wrote on Thu, 12 October 2006 18:40 Lesson learned: Leave your sql as-is. Someone else WILL fix it.

I never thought of it like that.
Re: Calculating running percentage discount [message #197881 is a reply to message #196431] Fri, 13 October 2006 01:37
 jaydave6 Messages: 4Registered: October 2006 Location: India Junior Member
That's nice of you Ross for sharing your experience.

It may sound surprising to you but my colleague here has made necessary changes in that SQL code to fit into the actual, more complicated requirement in less than half an hour!!!

Jay

"The trouble with doing something right the first time is that nobody appreciates how difficult it was!"
Re: Calculating running percentage discount [message #197897 is a reply to message #197881] Fri, 13 October 2006 03:13
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Senior Member
And how did your colleague enjoy that experience? Did you show them Barbara's 21 line PL/SQL and tell them that you implemented your code in preference to that?

Try another experiment. Choose another colleage who hasn't seen the code and doesn't know what you are working on.

Ask them if they can work out what the SQL is supposed to do.

Ross Leishman
 Previous Topic: How to delete Next Topic: utl_file
Goto Forum:

Current Time: Wed May 24 18:41:51 CDT 2017

Total time taken to generate the page: 0.13917 seconds