How to query tables to get cost of call

Question

What Query would I need to do to calculate cost of calls in mysql database?

Ive got two tables, one is a call log with call duration, and the other table is the tariff table with peak and offpeak rates, peaktime is 08:00:00 - 19:00:00 offpeak time is 19:00:00 - 08:00:00. rates for peak are say 10p a minute or 0.9992 a second or something on the lines of that. and offpeak 2p minute.

I want to know how to query the two tables to calculate the cost of call according to the call duration and the cost of the call - Rate per sec/minute.

Output would be on another table with CallerId, Source, Destination, call duration , cost of call

Answer 1

This seems relatively straight forward (which usually means I am missing something).

Starting with assumptions. Say the CALL_LOG table looks like this:

CallerId
Source
Destination
Duration
CallStartTime
CallStopTime

. . . and the TARRIFF table looks like this:

Id
RateType (Peak or OffPeak)
RateStartTime
RateStopTime
Rate

And let's assume you are using Oracle, since I don't see that specifically mentioned. But you say CDRs, so probably lots of records, so maybe Oracle. (NOTE: I removed the Oracle specific code and decided to do this as an inner join. Might be too slow though, depending on volume.)

And let's assume that the definition of an "off peak call" is a call that starts during an off-peak time, regardless of when it ends. (Note that this definition is critical to doing it correctly.)

Lastly, let's assume that there are only two rates, peak and off-peak, based on your comments. That seems strange, but ok. I would have thought that the times would differ by day, to allow for weekend rates, but you should be able to extrapolate.

So the cost for a call would then be

SELECT l.CallerId, 
       l.Source, 
       l.Destination, 
       l.Duration, 
       t.RateType, 
       l.Duration * t.Rate as Cost
FROM CALL_LOG l
  INNER JOIN TARRIF t
    ON l.CallStartTime BETWEEN t.RateStartTime and t.RateStopTime