The next step is to calculate the actual call periods. The idea is to compare the start and end times of the potential call periods with the start and end times of the complete calls, then determine the start and end times of the actual call periods accordingly. Run the script that Listing 6 shows to create the VActualCallPeriods view, which uses CASE expressions in the SELECT list to perform these calculations.
To calculate the start time of the actual call period (actualfrom), the first CASE expression checks whether the call started during the potential call period, and if so, returns the call start time:
WHEN
firstminute
BETWEEN
potentialfrom
AND
potentialto
THEN
firstminute
For example, user2's call start time (firstminute) on January 12, 2003, was 23:00. For the potential call period January 12, 2003, 22:00-23:59, 23:00 falls between 22:00 and 23:59, so 23:00 is returned as the start time of the actual call period (actualfrom). If the actual start time doesn't fall within the potential period, the second WHEN clause checks whether the potential period's start time occurs during the actual call. In that case, the code returns the potential call period's start time as the actual call period's start time:
WHEN potentialfrom BETWEEN
firstminute AND lastminute THEN
potentialfrom
Still discussing the same call, consider the potential period January 13, 2003, 00:00-7:59. The start time of this potential call (00:00) occurs during the complete call, so the code uses it as the actual call period's start time. Note that when none of the WHEN clauses in a CASE expression return true and no ELSE clause exists, the CASE expression returns NULL. In this case, the expression returns NULL when the potential call period doesn't overlap the complete call at all, such as with the potential call period January 12, 2003, 00:00-7:59.
In a similar manner, the second CASE expression calculates the actual call period's end time:
CASE
WHEN lastminute BETWEEN
potentialfrom AND
potentialto THEN lastminute
WHEN potentialto BETWEEN
firstminute AND lastminute
THEN potentialto
END AS actualto
I'll let you figure out the logic yourself this time. Run the following query against the VActualCallPeriods view, then examine the results, which Figure 4 shows:
SELECT username, actualfrom,
actualto, rate
FROM VActualCallPeriods
Rows with NULLs represent potential call periods that didn't overlap with the actual call. Other rows contain the start and end times of the actual call periods. For example, user2's call was split into four different actual call periods. For simplicity's sake, I inserted one call for each user as sample data, but the code would work correctly with many calls per user. To return the cost per user and call, run the following simple query, which sums the cost of each call:
SELECT username, firstminute,
lastminute,
SUM(DATEDIFF(minute,
actualfrom, actualto) + 1) AS
duration,
SUM((DATEDIFF(minute,
actualfrom, actualto) + 1) *
rate) AS cost
FROM VActualCallPeriods
GROUP BY username, firstminute,
lastminute
The code calculates the cost of each period as the duration in minutes multiplied by the period's rate. Note that the total duration is calculated here as the sum of all durations of actual call periods, even though it's a known value. You can verify the result by cross-checking the total durations in the result (which Figure 5 shows) against the durations inserted into the Calls table. You can also manually calculate one session's total cost by examining the output that Figure 4 shows and comparing it to the result that Figure 5 shows.
To calculate total duration and cost per user, simply remove all irrelevant columns from the GROUP BY clause and from the SELECT list and leave only the username column:
SELECT username,
SUM(DATEDIFF(minute,
actualfrom, actualto) + 1) AS
duration,
SUM((DATEDIFF(minute,
actualfrom, actualto) + 1) *
rate) AS cost
FROM VActualCallPeriods
GROUP BY username
Figure 1 shows the result, which is the final solution you're looking for.
A Matter of Practice
One secret to solving tough challenges such as this one is to master the basics by constant practice. At some point, you realize that solving most complex problems is merely a matter of choosing the right basic techniques and combining them wisely. The call-costs problem is a challenge, but the "ingredients" of the solution are simple: views, a simple auxiliary table, basic date-manipulation functions, basic aggregate functions, and a CASE expression. Constantly practice the basics, and you'll be able to solve virtually any problem.
End of Article
Prev. page
1
[2]
next page -->
