CODEWITHSUNDEEP
mysqlsql
Learner
Learner

Reputation: 21425

Get total cost for a customer call

I have 2 tables = customer and their call history

Now I want to charge them based on call duration and that too for a specific month say Jan 2015.

Here is the criteria to calculate the cost for calls -

A) For incoming calls, the charge is 1 unit per second. Example if the duration is 250 seconds then cost is 250

B) For outgoing calls, for the first 2mins, the cost is fixed at 500 units. for subsequent seconds the cost is 2 units per second.

Example if the outgoing duration is 5mins then cost is 500 units + 2*3*60 units = 860 units

Below are the tables:

customer table with columns id, name, phone

history table with columns id, incoming_phone, outgoing_phone, duration, dialed_on (YYYY-MM-DD)

I have come up with below queries for my conditions:

For incoming call cost:

select c.name, c.phone, h.duration as cost
from customer c join history h on c.phone = h.incoming_phone

When I run the above query I did not get any syntax errors.

For outgoing call cost:

select c.name, c.phone, CASE
    WHEN h.duration > 120 THEN 500 + 2*(h.duration-120)
    ELSE 2*(h.duration-120)
END; as cost
from customer c join history h on c.phone = h.outgoing_phone

When I run the above query I got syntax error like "ERROR 1109 (42S02) at line 1: Unknown table 'c' in field list"

I want to join these two queries and get the total cost and display the fields as name, phone, cost

I still need to add a condition for a specific month to restrict data for Jan 2015, but got stuck with the approach.

Upvotes: 1

Views: 10335

Answers (2)

Scott Forney
Scott Forney

Reputation: 1

I had a similar question in an evaluation with different table names and date. Here's how I sorted out the incoming vs outgoing records.

select c.name, c.phone_number, i.incoming_cost + o.outgoing_cost call_unit

from customer_detail c

left join 
(select c.phone_number, sum(duration) as incoming_cost 
from call_record r right join customer_detail c
on c.phone_number = r.incoming_number
where year(dialed_on)=2018
and month(dialed_on)=05
group by c.phone_number
) i on c.phone_number = i.phone_number

left JOIN
(select c.phone_number, sum(case when duration > 120 then 500+ 2*(duration-120)
    when duration <=120 then 500
    end) as outgoing_cost 
from call_record r right join customer_detail c
on c.phone_number = r.outgoing_number 
where year(dialed_on)=2018
and month(dialed_on)=05
group by c.phone_number
) o on c.phone_number = o.phone_number

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

The error is due to the extra semicolon ; after END.

Sounds like your final query will be this:

SELECT c.name, 
       c.phone, 
       SUM(CASE WHEN h.direction = 'in' THEN h.duration END) as IncomingCost,
       SUM(CASE WHEN h.direction = 'out' AND h.duration > 120 THEN 500 + 2*(h.duration-120)
                WHEN h.direction = 'out' AND h.duration <= 120 THEN 500
       END) as OutgoingCost,
       SUM(CASE WHEN h.direction = 'in' THEN h.duration END +
       CASE WHEN h.direction = 'out' AND h.duration > 120 THEN 500 + 2*(h.duration-120)
            WHEN h.direction = 'out' AND h.duration <= 120 THEN 500
       END) as TotalCost
FROM customer c 
JOIN (SELECT 'out' as directon, duration, dialed_on, outgoing_phone as phone 
      FROM history 
      WHERE YEAR(dialed_on) = 1995
      AND MONTH(dialed_on) = 1
      UNION ALL
      SELECT 'in' as direction, duration, dialed_on, incoming_phone as phone
      FROM history
      WHERE YEAR(dialed_on) = 1995
      AND MONTH(dialed_on) = 1
     ) h ON c.phone = h.phone
GROUP BY c.name,
         c.phone

Upvotes: 2

Related Questions