Home » SQL & PL/SQL » SQL & PL/SQL » SQL to calculate call cost for varying time durations (ORACLE 10G)
| SQL to calculate call cost for varying time durations [message #622780] |
Fri, 29 August 2014 07:21  |
 |
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
I have a table CALL_AUDIT and the records related to Call Data Records.
There is a plan i.e PLAN1, according to that charging is :-
First 120 seconds of the day @2 paisa/second and after that 1paisa/2second
Following is the query for table and data :-
CREATE TABLE CALL_AUDIT
(CALLINGNO NUMBER(10),
CALLED NUMBER (13),
CALLDATE VARCHAR2(14),
DURATION NUMBER(10),
DEDAMT VARCHAR2(10))
INSERT INTO CALL_AUDIT VALUES(9999999999,0911234567888,29082014114051,13,26);
INSERT INTO CALL_AUDIT VALUES(9999999999,0912234567889,29082014124152,58,116);
INSERT INTO CALL_AUDIT VALUES(9999999999,0913234567887,29082014134253,13,26);
INSERT INTO CALL_AUDIT VALUES(9999999999,0911234567888,29082014144354,101,104.5);
INSERT INTO CALL_AUDIT VALUES(8888888888,0912234567885,30082014074051,3,6);
INSERT INTO CALL_AUDIT VALUES(8888888888,0913234567884,30082014084152,68,136);
INSERT INTO CALL_AUDIT VALUES(8888888888,0914234567883,30082014094253,13,26);
INSERT INTO CALL_AUDIT VALUES(8888888888,0915234567882,30082014104354,101,105);
INSERT INTO CALL_AUDIT VALUES(8888888888,0911634567881,30082014114055,233,117);
INSERT INTO CALL_AUDIT VALUES(7777777777,0915234567886,31082014184051,3,6);
INSERT INTO CALL_AUDIT VALUES(7777777777,0916234567885,31082014194152,68,136);
INSERT INTO CALL_AUDIT VALUES(7777777777,0917234567884,31082014204253,13,26);
INSERT INTO CALL_AUDIT VALUES(7777777777,0918234567883,31082014214354,100,104);
INSERT INTO CALL_AUDIT VALUES(7777777777,0919634567882,31082014224055,233,117);
INSERT INTO CALL_AUDIT VALUES(7777777777,0919634567882,31082014235755,537,449);
INSERT INTO CALL_AUDIT VALUES(6666666666,0918234567883,31082014014051,23,46);
INSERT INTO CALL_AUDIT VALUES(6666666666,0917234567884,31082014024152,68,136);
INSERT INTO CALL_AUDIT VALUES(6666666666,0916234567885,31082014034253,13,26);
after inserting the data into table it will look like :-
The queris which i have made are:-
SELECT CALLINGNO,SUBSTR(CALLDATE,1,8) CALL_DATE,SUM(DURATION) TTL_DURATION,SUM(DEDAMT) TTL_DEDAMT
FROM CALL_AUDIT
GROUP BY CALLINGNO,SUBSTR(CALLDATE,1,8)
HAVING SUM(DURATION)>120
AND (CEIL((SUM(DURATION)-120)/2)*1)+240=SUM(DEDAMT)
UNION
SELECT CALLINGNO,SUBSTR(CALLDATE,1,8) CALL_DATE,SUM(DURATION) TTL_DURATION,SUM(DEDAMT) TTL_DEDAMT
FROM CALL_AUDIT
GROUP BY CALLINGNO,SUBSTR(CALLDATE,1,8)
HAVING SUM(DURATION)<=120
AND SUM(DURATION)*2=SUM(DEDAMT)
Note:- The query is working fine where the total duration of call in a day less than 120 seconds but not for duration greater than 120 seconds.
Let me manually explain the charging criteria where
callingno is 8888888888 , kindly remember Ist 120 seconds will be charged at 2paisa after that 1 paisa/2 seconds.
1. he made 1st call for 3 seconds i.e according to plan charging should be 6 paisa (3*2)
2. he made 2nd call for 68 seconds i.e according to plan charging should be 136 paisa(68*2)
3. he made 3rd call for 13 seconds i.e according to plan charging should be 26 paisa(13*2)
Note now 36 seconds are left out of 120seconds i.e (120-3-68-13)
4. he made 4th call for 101 seconds i.e 36 seconds will be charged at 2 paisa and remaining seconds at 1paisa/second , (note: if there is duration of call is 1 sec only then that will count as 2 second , if call duration is 13 then that will be taken as 7 not 6.5)
so the charging should be 36*2 + round(101-36)/2 = 72+33 i.e 105
5. he made 5th call for 233 seconds i.e according to plan charging should be 117 paisa
(round(233/2))
My Requirement is I want those records where the charging has been done as per plan.
Please Help.
Thanks
Lalit : Renamed topic title from "SQL QUERY FOR PARTICULAR CONDITION" to "SQL to calculate call cost for varying time durations".
-
Attachment: untitled.JPG
(Size: 61.88KB, Downloaded 1243 times)
[Updated on: Fri, 29 August 2014 11:46] by Moderator Report message to a moderator |
|
|
|
| Re: SQL QUERY FOR PARTICULAR CONDITION [message #622782 is a reply to message #622780] |
Fri, 29 August 2014 09:12  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Is the duration limit for calculation applied on every single call or the sum of duration of all calls?
I would simply have a case construct :
Case
When duration = 1
Then cost = 2
when duration between 2 and 120
Then cost = 2*duration
When duration > 120
Then cost = 2*120 + (duration - 120)/2
End cost_per_call
Edit : Added /2 since for > 120 case charge is 1 unit for 2 seconds.
[Updated on: Fri, 29 August 2014 09:15] Report message to a moderator |
|
|
|
| Re: SQL QUERY FOR PARTICULAR CONDITION [message #622783 is a reply to message #622782] |
Fri, 29 August 2014 09:37 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
Just as a separate issue, when you have a date/time value to save, always use a DATE or TIMESTAMP column, never store it as a character string. It not only allows junk to get into the field but it allows you to do simple date calculations.
|
|
|
|
|
|
| Re: SQL QUERY FOR PARTICULAR CONDITION [message #622787 is a reply to message #622784] |
Fri, 29 August 2014 10:41 |
 |
DrabJay
Messages: 32
Registered: May 2013
|
Member |
|
|
You should take into account Bill and Lalit's observations. However, you could use something like:SELECT callingno
,called
,calldate
,duration
,dedamt
, least(duration, greatest(120 - nvl(sum(duration) OVER (PARTITION BY callingno, substr(calldate, 1, 8) ORDER BY calldate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 0)) * 2
+ ceil((duration - least(duration, greatest(120 - nvl(sum(duration) OVER (PARTITION BY callingno, substr(calldate, 1, 8) ORDER BY calldate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 0))) / 2) calcamt
FROM call_audit_table s
ORDER BY callingno, calldate
|
|
|
|
|
|
| Re: SQL QUERY FOR PARTICULAR CONDITION [message #622791 is a reply to message #622787] |
Fri, 29 August 2014 10:55 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
@DrabJay,
I appreciate that you are trying to help OP with your posted code. But, this :
In my opinion, it defeats the purpose of suggesting OP that the date column MUST be a date data type.
I understand, it is not easy to move forward and to post a query at this stage when OP has not come back to feedback our suggestions, but, at least it should be mentioned that why the same bad practice was used. It's just my opinion.
Regards,
Lalit
|
|
|
|
| Re: SQL to calculate call cost for varying time durations [message #622797 is a reply to message #622780] |
Fri, 29 August 2014 11:50 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
@OP,
Just to remind you that I have renamed the topic title from "SQL QUERY FOR PARTICULAR CONDITION" to "SQL to calculate call cost for varying time durations".
In future, please try to mention an appropriate title for your new topics. It will help anybody searching for similar issue.
|
|
|
|
| Re: SQL to calculate call cost for varying time durations [message #622863 is a reply to message #622797] |
Mon, 01 September 2014 00:33 |
|
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
hi @everyone , its not a real table i am working on, there are around 61 columns in a real table, i picked only few columns and made dummy table , and defining varchar2 and date datatype column is not necessary here , i just want to calculate the total number of duration and total amount deducted against a callingno for a particular day (group by date),
and for that i am using substr(calldate,1,8 )
now let me explain me the issue i am facing
1. caller has made 1st call for 13 seconds then it will be count as 13 seconds
2. caller has made 2nd call for 107 seconds then it will be count as 107 seconds
(now user has utilised 120 seconds and according to plan the charges are 2paisa/second for Ist 120 seconds of a day)
3. caller has made 3rd call for 51 seconds then it will count as 52 seconds
(Because according to plan after 120 seconds the charges are 1paisa/2seconds so if a user has made call for only 1 sec then also it will be count as 2 seconds)
4. caller has made 4th call for 27 seconds then it will count as 28 seconds.
Now according to my query the total duration is (13 seconds+107 seconds+51 seconds+27 seconds)=198 seconds but accoding to plan it is (13+107+52+28)=200 seconds.
That is why i am getting wrong output.
One more issue is there If a user starts a call at day 1 and he continues that call till day 2 then according to my query the total duration of that call counted in a day 1.
for example
1. caller had made 1st call for 500 seconds on day 1 and the call date&time is 11:57:00 pm.
that means he talked for 180 seconds on day 1 and for 120 seconds on day 2.
so the charging should be (120*2)+((60/2)*1) + (120*2)
but according to my query it will be calculated as (120*2)+((180/2)*1).
I think i have explained every issue that i am facing.
& thanks for the comments everyone.
@DrabJay thanks for the query , but i am not familiar with functions that you used i n partition by clause for ex. least, preceding etc. but i will try to learn about these functions and try to solve my problem . thanks 
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 04:06:32 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
