| Downloads |
|---|
| 26163.zip |
Temporal calculations—that is, calculations based on dates and times—are often hard to put together. One reason they're so tricky is that more than one convention exists for expressing starting and ending dates and times. For example, we generally accept that January is the first month of the year and that the first month of the year has 31 days. However, in some financial calculations, October might be the first month of the fiscal year, or all months might have a fixed length of 30 days. Also, which day is the first day of the week, and which is last? In some countries, Monday is considered the first day of the week; in others, it's Sunday. In this column, I tackle a problem in which the last day of the week is dynamic—that is, passed as an argument—and the solution has to adjust itself accordingly. The date/time functions that SQL Server supplies don't cover all conventions for expressing dates and times, so you need to find a way of dealing with the variations. I'd like to thank Michael Arney, who sent me his ideas regarding the "last day of the week" problem. The solutions I present here, which combine Arney's ideas and mine, involve techniques for grouping data on a weekly basis when the first day of the week is dynamic.
Grouping Quantities by Week
Many variations of data analysis require you to summarize quantities or amounts on a temporal basis (i.e., based on time periods such as year, month, and so on). For example, you might need to find the number of orders each customer placed each quarter in 2002.
Suppose you have a Sales table in which the sales dates, amounts, and other sales details are recorded. If you needed to return yearly sales quantities from this Sales table, you could write a simple aggregate query. However, you need to group the sales by week. Your query has to return, for each week's end date, the total sales quantity for that week. However, the last day of the week is dynamic—that is, the client application passes it to your code as an argument. Run the script in Listing 1 to create and populate an abbreviated Sales table that contains only the two columns that are relevant for the example problem: salesdate and qty.
If the given last day of the week is Saturday, assuming the sample Sales table data, the result should look like Table 1. As Table 1 shows, the first week-end date for which sales exist in the Sales table falls on December 8, 2001. The sales for that week took place on December 3 and December 7, with quantities of 10 and 11, respectively. The second week-end date for which sales exist in the Sales table falls on December 15, 2001. The sales that belong to that week took place on December 10, December 11, and December 15, with quantities of 6, 21, and 3, respectively. Therefore, the summarized table shows a total quantity of 21 for the week that ends December 8, 2001, and a total of 30 for the week that ends December 15, 2001.
However, if the given last day of the week is Friday, the result would look like Table 2, page 14. I'll let you do the math yourself to verify that the results are correct. Note how the output changed for the same base data, with only the week-end day changing from Saturday to Friday. Let's see how you can create a solution to the "last day of the week" problem first by using an auxiliary table, then by using a base date as an argument.
Solution 1: Using an Auxiliary Table
The first approach, which consists of three steps, is conceptually simple but takes some effort to implement. First, for each day of the week, you calculate the difference between that day and the day you've chosen as the last day of the week. Let's call that difference Diff. For example, if you choose Friday as the last day of the week, a Diff value of 5 would represent Sunday, a value of 4 would represent Monday, and so on. For step 2, you add the appropriate Diff value to each sales date, according to the weekday of the sales date. For example, if a sales date falls on Sunday, you would add 5 days to it. Let's call the result column week_end. Note that by following these two steps, all sales dates belonging to the same week will have the same week_end value. The final step is to group the results by week_end.
Let's look more closely at each of these steps, beginning with Step 1. Run the script that Listing 2 shows to create and populate the auxiliary table WeekDays. WeekDays contains two columns: dayname stores the name of the week day, and dayid contains consecutive integer values. I used 1 as the dayid for Sunday and 7 for Saturday, but you can use other values as long as they represent a cycle of 7 consecutive days. For example, you could choose 0 through 6. To calculate the Diff value for each weekday in WeekDays when Friday is the specified last day of the week, you can create code based on the following pseudo code:
DECLARE @lastweekday AS VARCHAR(9) SET @lastweekday = 'Friday' SELECT dayname, (<lastweekday's dayid> - dayid + 7) % 7 AS diff FROM WeekDays
An entire mathematical theory deals with a finite set of numbers laid on a clock-like axis representing a cycle. In this case, numbers from 1 to 7 represent the cycle of days in a week. By subtracting the current day's dayid from the week-end day's dayid value, adding 7, and calculating the remainder (modulo) of the result when the sum is divided by 7, you get the difference between the current day and the next week-end day. In other words, you get the Diff value. In T-SQL terms, your code would look like this:
DECLARE @lastweekday AS VARCHAR(9)
SET @lastweekday = 'Friday'
SELECT dayname,
((SELECT dayid
FROM WeekDays
WHERE dayname = @lastweekday) - dayid + 7) % 7 AS diff
FROM WeekDays
Table 3 shows the results of executing this code.
Now, change the @lastweekday value to 'Saturday' and run the code again. You'll get the results that Table 4 shows. Note how the results change to reflect a different week-end day. To complete the solution to this problem, you need to implement steps 2 and 3. You can use a derived table containing the query that implements Step 1 by calculating the Diff value for each week day and join the derived table to the Sales table based on the day of the week. You can use the DATENAME() function in the JOIN condition to calculate the current sales date's day name and compare the result to the day name from the derived table. You then add Diff to each sales date and use the result in the GROUP BY clause. Listing 3 shows the complete solution.
You can check your results against those in Table 1 to make sure they match. And you can experiment with the week-end day to see how the results change when you change the last day of the week.
Solution 2: Using a Base Date as an Argument
A second approach to solving the problem requires you to specify as an argument a base date that falls on the same day of the week as the week-end day, instead of specifying the week-end day itself. For example, instead of supplying 'Saturday' as an argument, you specify a base date that falls on a Saturday, such as '20010106'. To get the same week-end date for all rows that belong to the same week, calculate the difference in days between the base date and each sales date, add 6, and perform an integer division by 7. (Remember that integer division truncates the fraction.)
With this calculation, all sales dates belonging to the same week will get the same result. Take Sunday, January 7, 2001, for example. The difference in days between it and the base date, January 6, 2001, is 1. Adding 6 to that difference gives you 7, and dividing that result by 7 gives you 1. If you perform the same calculation with the following Monday through Saturday, you'll notice that they all result in 1. Now perform the calculation for Sunday, January 14, 2001, and the following Monday through Saturday. These calculations all return 2. You can consider the value that you calculate to be the week number, starting with 0 as the week number of the base date. If a sales date falls 7 days or more before the supplied date, the week number calculated for it would be negative, which is how the calculation should work.
Once you have a logical or mathematical solution to a problem, translating the idea to T-SQL is a simple task. Listing 4 shows the complete solution. In Listing 4's code, the derived table WN uses the calculation I described for using a base date to calculate the week number for each sales date. The outer query groups the result by week number (weeknum). Note that the last week-end date is calculated in the SELECT list. Because weeknum is known, the expression simply adds (weeknum * 7) days to the base date, resulting in the correct week-end date. Again, you can experiment with different input arguments to see how the results change.
