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:
- The
customertype
- 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?
精彩评论