T-SQL programmers frequently face tricky date- and time-related challenges. And solving these temporal problems often requires combining several different techniques that you've learned from experience. Let's look at an interesting temporal challenge that I call the call-costs problem. To solve the problem, you might find several techniques that I discussed in past columns useful, such as using auxiliary tables and a step-by-step modular approach that uses views.
The Call-Costs Problem
Internet service providers (ISPs) often charge different rates for user dial-up calls during different periods of the day. ISPs usually charge higher rates during periods of high traffic than during periods of low traffic. For example, suppose a fictitious ISP, LessWire, charges 2 cents per minute from 22:00 through 07:59 and 5 cents per minute from 08:00 through 21:59. LessWire stores call information in a table named Calls. Running the script in Listing 1 creates the Calls table and populates it with sample data.
Each row in Calls contains the caller's user name in the username column, the call start date and time in the calldate column, and the call length in minutes in the duration column. Note that the calldate column is defined as smalldatetime, meaning that it's accurate to the minute. That accuracy is especially convenient in the solution to this problem because you need to perform calculations involving whole minutes. Your task is to calculate the total duration and cost of calls for each user. For the given sample data, the output should look like Figure 1. Try to make the solution flexible in the sense that you could easily change rates and add or change the start and end times of rate periods.
The Solution
First, you need to decide where and how to store the information about rates and rate periods. Hard-coding them into your queries is a bad choice because this approach requires tedious, awkward code maintenance with every rate change. A better idea is to create a Rates table in which you can store all rate-related information and have your queries refer to that table. Run the script that Listing 2 shows to create a Rates table and populate it with the periods and rates I gave earlier.
The fromtime and totime columns store (as a smalldatetime data type) the minute of day the rate period starts and ends, and the rate column stores the period's rate. Note that I keep three rate periods in the Rates table even though only two exist. I did this because one of the rate periods (22:00 through 07:59) spans 2 days, and I wanted to express all rate periods in one day's boundaries to avoid unnecessary complications in the solution. Another point to keep in mind is that the smalldatetime data type stores both a date and a time, and when you enter only the time portion, as I did in the fromtime and totime columns, SQL Server stores the base date January 1, 1900. I didn't explicitly specify the date to emphasize the fact that the time periods aren't specific to a certain date.
After you have the rate periods organized in a table, you can start working on the queries. You have to split each call into periods according to the day's rate periods. A view containing each call's start and end times can help. I created a view called VStartEndCalls that has columns called firstminute and lastminute, representing the call's inclusive start and end times to the minute. Because the date portion of the rate periods' start and end times is an old base date and I knew I'd have to represent these time periods in current terms (date of call), I also calculated the difference in days between the base date and the call date. I named the result column diff. You can run the script that Listing 3 shows to create the VStartEndCalls view. Figure 2 shows the result of a SELECT * query against this view.
If you're not sure about the usefulness of the diff value, bear with me while I explain the next step, which is to generate all potential or possible call periods for each day a call spanned. In a later step, you can discard or revise the potential call periods to reflect the actual ones. For example, user2 started a call on February 12, 2003, at 23:00, and the call took 1440 minutes (24 hours), spanning 2 days. Each day has three possible rate periods, so this call has three potential periods on February 12 and three potential periods on February 13. I created a view called VPotentialCallPeriods that returns, for each call, all potential call periods for each day the call spanned. To generate for each call as many rows as the number of days the call spanned, I used an auxiliary table called Nums that contains a sequence of consecutive integers. I populated the Nums table with values in the range 1 to 1000, which is much more than the maximum number of anticipated days a call might span. The script in Listing 4 creates and populates the Nums auxiliary table, and the script in Listing 5 creates the VPotentialCallPeriods view.
The query in the VPotentialCallPeriods view definition performs a join between the VStartEndCalls view and the Nums auxiliary table. The purpose of the JOIN condition
n <= DATEDIFF(day, firstminute,
lastminute) + 1
is to duplicate each call in VStartEndCalls as many times as the days the call spanned. For example, this join would generate two rows for the call by user2 that spanned 2 days--one row where n=1 and one where n=2. The query in the view cross-joins the result with the Rates table to match all possible rate periods with each day of each call. This means that user2's call appears in the result of the three-way join in six rows because it was matched with two rows from Nums (n = 1, 2), each of which was matched with three rows from Rates: 1 x 2 x 3 = 6.
Now, you just have to write expressions in the query's SELECT list that calculate the start and end times of the potential call periods. The following expressions calculate the start and end times of a potential call period (the potentialfrom and potentialto result columns); see if you can figure out how the calculations work:
fromtime + diff + n - 1 AS
potentialfrom
totime + diff + n - 1 AS
potentialto
Recall that diff is the difference in days between the call date and the base date (January 1, 1900) and that rate period start and end times (fromtime and totime) are expressed as time values with the same base date. By adding diff to fromtime and totime, you get those times in terms of the call's start date. By adding n and subtracting 1, you get those times in the correct day during the call. The whole process is probably not simple to grasp without looking at the results of a query against the VPotentialCallPeriods view. Run the following query, look at the results in Figure 3, page 16, and reread the explanation of this step:
SELECT username, potentialfrom,
potentialto, rate
FROM VPotentialCallPeriods
Notice, for example, that the code split user2's call, which I discussed earlier, into six potential call periods--three in each day the call spanned (00:00 to 07:59, 08:00 to 21:59, and 22:00 to 23:59).
Prev. page
[1]
2
next page 
