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