Home » SQL & PL/SQL » SQL & PL/SQL » Volume Apportioned to right volumen bands (Oracle 11g)
| Volume Apportioned to right volumen bands [message #648582] |
Thu, 25 February 2016 15:51  |
|
|
Hi Experts,
I have an issue with a logic that i tried to resolved but unsuccessful.....
I have daily source volume that need to be apportioned correctly to its volume bands, such that right charges can be applied
Source table Structure:
Create table DAILY_VOL (
CTR Varchar2(10),
YEAR_MONTH Varchar2(10)
);
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Insert into DAILY_VOL values ('T1', '201602');
Look up tables structure:
Create TABLE Unit_Charges (
CTR Varchar2(10),
VOL_BAND number,
MIN_VOL number,
MAX_VOL number,
Charges number
);
Insert into Unit_Charges values ('T1', 1, 1, 10, 100;
Insert into Unit_Charges values ('T1', 2, 11, 20, 10);
Insert into Unit_Charges values ('T1', 3, 21, 30, 5);
Insert into Unit_Charges values ('T1', 3, 31, 40, 2);
Suppose that there are thirty-five (35) transactions for a type T1 in a month and that the volume bands are as defined by the minimum and maximum volumes.
Then the total Charge is calculated by multiplying the volume of transaction within each band by the relevant Unit Price.
Final out come expected:
Band Min Max Charges Volume VolumeCharge
Vol Vol
1 1 10 £100.00 10 £1,000.00
(£100 x 10)
2 11 20 £10.00 10 £100.00
(£10 x10)
3 21 30 £5.00 10 £50.00
(£5 x 10)
4 31 40 £2.00 5 £10.00
(£2 x 5)
Total 35 £1,160.00
I had done few quires with analytical functions using Range Between Min_Vol FOLLOWING AND UNBOUNDED FOLLOWING
But nothing workout ....
Highly appropriate you help on this .
Thank you
|
|
|
|
| Re: Volume Apportioned to right volumen bands [message #648585 is a reply to message #648582] |
Thu, 25 February 2016 18:56  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl> -- test data:
SCOTT@orcl> SELECT ctr, year_month, COUNT(*)
2 FROM daily_vol
3 GROUP BY ctr, year_month
4 /
CTR YEAR_MONTH COUNT(*)
---------- ---------- ----------
T1 201602 35
1 row selected.
SCOTT@orcl> SELECT * FROM unit_charges
2 /
CTR VOL_BAND MIN_VOL MAX_VOL CHARGES
---------- ---------- ---------- ---------- ----------
T1 1 1 10 100
T1 2 11 20 10
T1 3 21 30 5
T1 4 31 40 2
4 rows selected.
SCOTT@orcl> -- query:
SCOTT@orcl> SELECT dv.ctr, dv.year_month,
2 uc.vol_band "Band", uc.min_vol "Min", uc.max_vol "Max", uc.charges "Charges",
3 COUNT(*) "Volume", COUNT(*) * charges "VolumeCharge"
4 FROM (SELECT ctr, year_month,
5 ROW_NUMBER () OVER (PARTITION BY ctr, year_month ORDER BY ROWID) rn
6 FROM daily_vol) dv,
7 unit_charges uc
8 WHERE dv.ctr = uc.ctr
9 AND dv.rn BETWEEN uc.min_vol AND uc.max_vol
10 GROUP BY dv.ctr, dv.year_month,
11 uc.vol_band, uc.min_vol, uc.max_vol, uc.charges
12 ORDER BY dv.ctr, dv.year_month, uc.vol_band
13 /
CTR YEAR_MONTH Band Min Max Charges Volume VolumeCharge
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
T1 201602 1 1 10 100 10 1000
T1 201602 2 11 20 10 10 100
T1 201602 3 21 30 5 10 50
T1 201602 4 31 40 2 5 10
4 rows selected.
|
|
|
|
| Re: Volume Apportioned to right volumen bands [message #648606 is a reply to message #648585] |
Fri, 26 February 2016 10:29 |
|
|
Thank you once again Barbara for you kind response...
But my sincere apologies the volume we receive is already exaggerated and we need to literally split that in to different volume bands
sample data
Create TABLE Unit_Charges (
CTR Varchar2(10),
VOL_BAND number,
MIN_VOL number,
MAX_VOL number,
Charges number
);
Insert into Unit_Charges values ('T1', 1, 1, 10, 100;
Insert into Unit_Charges values ('T1', 2, 11, 20, 10);
Insert into Unit_Charges values ('T1', 3, 21, 30, 5);
Insert into Unit_Charges values ('T1', 3, 31, 40, 2);
Create table DAILY_VOL (
CTR Varchar2(10),
YEAR_MONTH Varchar2(10),
Volume number
);
Insert into DAILY_VOL values ('T1', '201602',57);
Insert into DAILY_VOL values ('T1', '201603',4);
The unit charges stays that same but the daily volume is aggregated and this need to be apportioned
example out of 57 in feb 2016 10 should be approached to band 1 (1- 10)
and next range that is 11 - 20 in band 2 ... so on...
would highly appreciate if you can help me on this tooo...
Thank you
|
|
|
|
| Re: Volume Apportioned to right volumen bands [message #648607 is a reply to message #648606] |
Fri, 26 February 2016 14:00 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl> -- test data:
SCOTT@orcl> SELECT * FROM daily_vol
2 /
CTR YEAR_MONTH VOLUME
---------- ---------- ----------
T1 201602 57
T1 201603 4
2 rows selected.
SCOTT@orcl> SELECT * FROM unit_charges
2 /
CTR VOL_BAND MIN_VOL MAX_VOL CHARGES
---------- ---------- ---------- ---------- ----------
T1 1 1 10 100
T1 2 11 20 10
T1 3 21 30 5
T1 4 31 40 2
4 rows selected.
SCOTT@orcl> -- query:
SCOTT@orcl> SELECT dv.ctr, dv.year_month,
2 uc.vol_band "Band", uc.min_vol "Min", uc.max_vol "Max", uc.charges "Charges",
3 COUNT(*) "Volume", COUNT(*) * charges "VolumeCharge"
4 FROM (SELECT ctr, year_month, COLUMN_VALUE rn
5 FROM daily_vol,
6 TABLE
7 (CAST
8 (MULTISET
9 (SELECT LEVEL
10 FROM DUAL
11 CONNECT BY LEVEL <= volume)
12 AS SYS.ODCINUMBERLIST))) dv,
13 unit_charges uc
14 WHERE dv.ctr = uc.ctr
15 AND dv.rn BETWEEN uc.min_vol AND uc.max_vol
16 GROUP BY dv.ctr, dv.year_month,
17 uc.vol_band, uc.min_vol, uc.max_vol, uc.charges
18 ORDER BY dv.ctr, dv.year_month, uc.vol_band
19 /
CTR YEAR_MONTH Band Min Max Charges Volume VolumeCharge
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
T1 201602 1 1 10 100 10 1000
T1 201602 2 11 20 10 10 100
T1 201602 3 21 30 5 10 50
T1 201602 4 31 40 2 10 20
T1 201603 1 1 10 100 4 400
5 rows selected.
|
|
|
|
| Re: Volume Apportioned to right volumen bands [message #648636 is a reply to message #648607] |
Sat, 27 February 2016 11:59 |
|
|
Thank you once again .... this is really appreciated.
I believe I am doing bit wrong here used your query and tired to change this with my real data set, but is am getting wrong results
SELECT dv.ctr, dv.MONTH_WID,
uc.VOLUME_BAND "Band", uc.MIN_VOLUME "Min", uc.MAX_VOLUME "Max", uc.UNIT_PRICE "Charges",
COUNT(*) "Volume", COUNT(*) * UNIT_PRICE "VolumeCharge"
FROM (SELECT COMMAND_TYPE,PRIORITY_CODE,CTR, MONTH_WID, COLUMN_VALUE rn
FROM (SELECT A.COMMAND_TYPE,A.PRIORITY_CODE, A.CTR, C.ROW_WID AS MONTH_WID,B.MSG_COUNT
FROM SMODS.ODS_MTR A JOIN SMODS.ODS_MTS_DV B ON (A.ROW_WID=B.MTR_WID)
JOIN SMDWH.W_MONTH_D C ON (TO_NUMBER(TO_CHAR(B.GW_TIMESTAMP_DAY,'YYYYMM'))=C.ROW_WID)
WHERE CTR= 'T1') Daily_VOL ,
TABLE
(CAST
(MULTISET
(SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= MSG_COUNT)
AS SYS.ODCINUMBERLIST))) dv,
SMDWH.wc_mtr_unit_chg_d uc
WHERE dv.ctr = uc.ctr
AND dv.rn BETWEEN uc.MIN_VOLUME AND uc.MAX_VOLUME
GROUP BY dv.ctr, dv.MONTH_WID,uc.VOLUME_BAND, uc.MIN_VOLUME, uc.MAX_VOLUME, uc.UNIT_PRICE
ORDER BY dv.ctr, dv.MONTH_WID, uc.VOLUME_BAND
/
Basically the 'daily_vol' source that I shared comes from three different tables (subquery - highlighted in orange) and that's not working right
I had even tried to use With clause / Global temporary table option even that not working as expected.
highly appreciate your help again - I am not feeling good about coming back again and again, but honestly I am completely blocked.
thank you
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 21:57:01 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
