Solution 2
Noeth's solution uses an approach similar to the one in Solution 1, which uses a differentiating factor. Running the script that Listing 5 shows creates the VSalesDiff view, which implements the first step in the solution. For each month, the query in the VSalesDiff view returns the difference between that month's quantity and the preceding month's quantity, plus the corresponding trend. The query uses a derived table that returns the sales month value and the difference between the month's quantity and the preceding month's quantity, using the same trick I use in Listing 2. The outer query uses a CASE expression to determine the trend: positive value = Up, zero value = Same, and negative value = Down. Web Table 1 (available at http://www.sqlmag.com, InstantDoc ID 23846) shows the output of a SELECT * query against the VTrendsDiff view.
Listing 6 shows the second and last step. The code implements the main logic of the query in the derived table T, where it calculates the differentiating factor—groupcol. In this case, for each row in VSalesDiff, a subquery returns the minimum sales month of the rows that have a greater sales month and a different trend than the row in the outer query. Let's focus again on differentiating between the group of months January 2000—April 2000 and the group December 2000—February 2001. The differentiating factor for each row in the first group is the minimum sales month value of the rows with a greater sales month value and a different trend, which is 200005. The differentiating factor for each row in the second group is 200103. Then, you just have to group the result by the differentiating factor—groupcol—and return the minimum and maximum sales month values to mark the boundaries of the range. The query also returns, for each group, the minimum trend value. Because Noeth didn't include the trend in the GROUP BY clause, he has to use an aggregate function such as MIN() to fetch the trend. All the trend values are the same in each group, so you can use MAX() instead of MIN() or, alternatively, include the trend in the GROUP BY clause.
Solution 3
Guerrero supplied the third solution, which approaches the problem differently from the previous solutions. The first step is creating the VSalesTrends view, as Listing 7 shows. The query in the VSalesTrends view performs a three-way join. The joins correlate each row from Sales with the rows of the preceding and following months. The DATEDIFF() function verifies that the difference is 1 month (in the case of the join that locates the following month) and -1 month (in the case of the join that locates the preceding month). Because DATEDIFF() expects a complete date (including year, month, and day), you can't just pass a value containing only the year and month, such as the one in smonth. The trick Guerrero uses here is to concatenate "01" to the existing smonth value to signify the first day of the month.
In the WHERE clause, Guerrero uses the SIGN() function to determine the trends of the current month and the following month, then filters only the rows in which the month's trend is different from the following month's trend. He uses a left outer join to ensure that the first sale and the last sale won't both disappear from the output. Remember that an inner join doesn't return rows from one input if the row has no matching row in the other input. In the Sales table instance with the alias S1, the first sales row has no matching preceding sale in the Sales table instance with the alias S2, and the last sales row in S1 has no matching following sale in S3. Guerrero uses COALESCE() for the same reason—to replace a NULL value, representing a nonmatch returned by the left outer join, with a 0 value. Without this trick, the first and last sales rows in S1 would disappear from the output. Web Table 2 shows the output of a SELECT * query against the VSalesTrends view.
Web Listing 1 shows the second and last step. Here, the main logic is in the SELECT list. Most of the work, which includes calculating the sales month groups and the trend, was already done in the VSalesTrends view. The SELECT list uses a simple CASE expression to format the trend value, then returns sm1 as the to_month value of the range. Another CASE expression determines the range's from_month value. Sm2 holds the minimum smonth value of the next range of months. The CASE expression that calculates the from_month values uses a subquery to retrieve the sm2 value of the previous range. The only special case is in the first row in Web Table 2's output, where the trend is NULL because there's no previous month for the first month. In such a case, the trend is Unknown, and the from_month value is the same as the to_month value—in other words, sm1.
You can find information about using these solutions in the Web-exclusive sidebar "Practical Implementation," InstantDoc ID 23951. The examples in this article provide further proof that most problems have adequate set-based solutions. Thanks for all your comments. And let me know how you fared with this month's puzzle.
End of Article
Prev. page
1
[2]
next page -->
