Home » SQL & PL/SQL » SQL & PL/SQL » Calculating running percentage discount
| Calculating running percentage discount [message #196431] |
Thu, 05 October 2006 05:58  |
jaydave6
Messages: 4
Registered: 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: 3728
Registered: 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 #196732 is a reply to message #196623] |
Fri, 06 October 2006 22:16 |
 |
Barbara Boehmer
Messages: 9100
Registered: 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: 3728
Registered: 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: 9100
Registered: 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: 4
Registered: 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: 3728
Registered: 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:
- 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.
- Post it on OraFAQ Experts Forum and ask for help.
- Attempt both of the above, fail, and then check themselves into a mental health institution.
- Fire bomb your house.
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: 3728
Registered: 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 #197881 is a reply to message #196431] |
Fri, 13 October 2006 01:37 |
jaydave6
Messages: 4
Registered: 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: 3728
Registered: 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
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 15:39:51 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
