Give cursors the slip with these new moves
As I've discussed a lot recently, sometimes a cursor-based (rather than set-based) solution seems to be the simplest approach to solving a problem but isn't. In previous columns, I demonstrated some techniques that you can use to switch to set-based programming. I've received enthusiastic responses from readers who have shared their experiences of converting code that used cursors to code that implements a set-based approach. Readers also told of their attempts to solve the problems I presented before looking at the solutions. Your interest inspired me to give you a puzzle to test your skills in set-based coding and discuss a few possible solutions. I warn you that this puzzle is tough. Make sure you have enough free time to work on it. I presented this puzzle in a private SQL Server trainers' forum and received a couple of interesting solutions, which I also share with you. I'd like to thank SQL Server MCTs Dieter Noeth and Fernando G. Guerrero, who let me share their solutions with you.
The puzzle involves a sample Sales table, which you can create and populate by running the script that Listing 1 shows. I use the tempdb database, but you can revise it to any database you choose. The goal is to identify ranges of months with similar sales trends. A month's trend is determined by the month's quantity compared with the preceding month's quantity. A month's trend can be Up, Down, Same, or Unknown. For example, December 1999's trend is Unknown because it's the earliest month in the Sales table. January 2000's trend is Up because its quantity (110) is greater than December 1999's quantity (100). Similarly, you can determine May 2000's trend (Same) and June 2000's trend (Down).
Determining each month's trend is a fairly simple task; however, the puzzle requires the output to show ranges of months with similar trends, as Table 1 shows. For example, the months January 2000 to April 2000 are collapsed to a single range because all the months in that range have the same trend (Up). May 2000's trend is Same, meaning that it had the same sales value as April; note that May 2000 appears in its own range in the output because the adjacent months (April 2000 and June 2000) have different trends. Now you have all the information you need to start working on the puzzle. Oh yes, and cursors aren't allowed.
Solution 1
The first solution is the one that I came up with. As always, the best way to solve problems is to solve them in steps. The first step, which Listing 2, page 18, shows, is to create a view that identifies each month's trend. I use a subquery in the view's SELECT list to retrieve the current month's sales quantity along with that of the preceding month. I subtract the subquery's return value from the current month's quantity, wrapping the result with the SIGN() function. The SIGN() function returns -1 for a negative value (Down trend), 0 for zero (Same trend), and 1 for a positive value (Up trend). Table 2 shows the output of a SELECT * query against the VTrends view.
The next and most problematic step is to distinguish between one consecutive group of months with similar trends and other groups with the same trends. One way to express the difference is to write a query against VTrends that counts the number of rows that have a sales month greater than or equal to the current row's sales month but that have a different trend value. At this point, the explanation probably sounds like gibberish. To understand the trick I use, look at the view VTrendsDiff, which Listing 3 shows, and at Table 3, which shows the output of a SELECT * query against the view.
As an example, see whether you can tell the difference between the group of months January 2000—April 2000 and the group December 2000—February 2001, which have the same trend (Up). With the output in Table 3, it's easy. Each row in the first group has a differentiating factor of 11, which means that 11 rows in the Sales table have the same or greater date and a trend other than 1. A differentiating group factor is a value that helps you distinguish between one group and another. Each row in the second group has a differentiating factor of 5. The last step is fairly easy: a simple GROUP BY query that returns the minimum and maximum sales month (smonth) values for each group of differentiating factor plus trend, as Listing 4 shows. Running Listing 4's query produces the output we were trying to get.
Prev. page
[1]
2
next page 
