Many of you sent me solutions to puzzles I presented in past columns. I presented two of the best ones for the relational-division puzzle last month in "Try, Try Again" (InstantDoc ID 39604). This time, let's look at reader solutions to two puzzles that involved tracking dates and times. First, I discuss a solution to the February 2003 "Counting the Costs" problem (InstantDoc ID 27592) sent in by Scott Smith, a principal architect at M Systems Group, Toronto. Then, I show you a solution to the January 2003 Working Days problem (from "No Table? No Problem" InstantDoc ID 27051) sent in by Sergiy Korovin of Q-Sign Pty, Greensborough, Australia. Smith's and Korovin's solutions have something in common: They use auxiliary tables, which allow simple, efficient solutions.
Counting the Costs (February 2003)
The problem I presented in February involved calculating the costs of users' dial-up calls to an ISP. Each user's call information—including user name, call start time, and duration in minutes—is recorded in a Calls table. The ISP charges different rates per minute depending on when the calls occur. A table called Rates tracks the start and end times for the rate periods and the rates per minute for each period. Run the code in Listing 1 to create and populate the Calls and Rates tables. The task: Calculate the total duration and cost of all calls for each user. Figure 1 shows the desired results.
Smith slightly reformatted the Calls table by storing call start and end times instead of start time and call duration. Running the code in Listing 2 recreates the Calls table with the new format. This minor change allows simpler and more readable queries later. The key element in this solution is an auxiliary table called RatesExpand.
The Rates table holds the ISP's daily rate periods, but it doesn't hold rate periods for specific days. The Rates-Expand table expands the Rates table by holding the rate periods for the specific days you want to calculate the costs for. For example, suppose you want to calculate the call costs for February 2003. In that case, the Rates-Expand table would hold the rate periods for each of the 28 days in February. With three rate periods each day, you should have 28 x 3—that is, 84—rows in the table. Before creating the RatesExpand auxiliary table, run the code that Listing 3 shows to create an auxiliary table called Nums and populate it with 1000 consecutive integers. You use this table in the code that populates the RatesExpand table. Now, run the code that Listing 4 shows to create the RatesExpand table and populate it with the specific rate periods during February 2003.
The code in Listing 4 first creates the RatesExpand table, then populates it by cross-joining the Rates table with the Nums table. The cross-join multiplies each row in Rates by the number of rows in Nums. The filter in the WHERE clause removes duplicates when n is greater than the number of days in February 2003, so each row in Rates is multiplied only 28 times. The expressions in the SELECT list calculate each rate period's start and end times. For example, the following expression calculates a rate period's target start time:
DATEADD(minute,
DATEDIFF(minute, '19000101', fromtime),
DATEADD(day, n-1, '20030201')) AS fromdatetime
The n column in the Nums table represents each day in February. The expression adds n-1 days to February 1 to calculate the target date. It then adds to the expression's intermediate result the time portion of the rate period in minutes. Because SQL Server doesn't have a separate data type for time only, the Rates table stores the rate periods' start and end times in terms of the base date January 1, 1900. To add the right time portion to the target date, you calculate the difference in minutes between the rate period's fromtime and midnight, both in terms of the base date. You calculate the target end time of a rate period similarly, but specifying the Rates table's totime column instead of the fromtime column.
Prev. page
[1]
2
3
next page 
