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
Prev. page
[1]
2
next page 
