Calculate cost of a phone call in SQL

Q1:

A: There are two tables in a telecom SQL Server database – Customers and Rates as shown below:

Customers

PK  CustomerPhoneNumber   varchar(15)
    CustomerType          int              -the type of customer

Rates

FK  CustomerType          int              - the type of customer
    CountryCode           varchar(4)       – the country calling code
    Rate                  float            - the rate per minute of phone call

Example country codes:

1 – USA
1809 – Dominican Republic
44 – Great Britain
359 – Bulgaria

So a phone number in USA would be 13104405609.

As shown in the table the rates depend on the customer type and the country called.

Given the full originating and destination phone numbers (including country codes) of a call and its duration in minutes, write a single SQL statement to calcula开发者_如何学编程te the cost of the call.

For convenience, let the parameters for the SQL statement be called @FromPhoneNumber, @ToPhoneNumber, @Duration.

---

SELECT CostofCall = @Duration * Sum(Rate)
FROM
   Customers C
   INNER JOIN Rates R ON C.CustomerType = R.CustomerType
WHERE
   C.CustomerPhoneNumber IN (@FromPhoneNumber, @ToPhoneNumber)

---

If the total rate is the rate for the originating country + the rate for the destination country

(btw, this makes no sense from a business model perspective as that many discrete rates would not be controlled or applied by any one company)

But if it was, then the SQL would be:

 Select @Duration *
  ((Select fR.Rate
    From Customers fC Join Rates fR 
      On fR.CustomerType = fC.CustomerType
    Where fC.CustomerPhoneNumber = @FromPhoneNumber) 
    +
   (Select tR.Rate
    From Customers tC Join Rates tR 
      On tR.CustomerType = tC.CustomerType
    Where tC.CustomerPhoneNumber = @ToPhoneNumber))

---

This was my take:

SELECT r.rate * @Duration
  FROM CUSTOMERS c
  JOIN RATES r ON r.customertype = c.customertype
              AND (LEFT(r.countrycode, 1) = LEFT(@ToPhoneNumber, 1)
                OR LEFT(r.countrycode, 2) = LEFT(@ToPhoneNumber, 2)
                OR LEFT(r.countrycode, 3) = LEFT(@ToPhoneNumber, 3)
                OR LEFT(r.countrycode, 4) = LEFT(@ToPhoneNumber, 4))
 WHERE c.customerphonenumber = @FromPhoneNumber

I took the @FromPhoneNumber to be how to find the specific customer. To find out what rate that customer was going to be charged, you need to what rate is associated to the customer based on:

1. The customertype
2. The countrycode for the number being called

Because the data type of countrycode is VARCHAR(4), and none of the data types of the parameters are defined - assumptions were made. The code isn't entirely safe, but the idea is that only one rate should be returned because codes should be unique.

Can someone explain to me why the other answers combine the rates? Since when are you charged for both directions on a phone call?