TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
 Specialized Dev Zones Research Center eBook Library .NET Java C++ Web Dev Architecture Database Security Open Source Enterprise Mobile Special Reports 10-Minute Solutions DevXtra Blogs Slideshow

# It All Adds Up : Page 2

## Use SQL Server 2005's Common Table Expressions, UNPIVOT, and the ROW_NUMBER() function to solve an interesting problem.

 by Tom Moreau
 Sep 4, 2007
 Page 2 of 2

### WEBINAR:On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning

Upping the Ante
Let's up the ante a bit. Suppose you want to know the net change between any two dates. You'll need to find the total for each date and then take the difference between the two. Listing 2 shows a solution that does not use variables because you can use set-level SQL to pull this off. I call this a "bookend query." Note the symmetry of the solution. Only the ORDER BY clauses in the SELECT TOP 1's differ. Also, the MAX(EventDate) in the BETWEEN predicates returns you the latest EventDate on or before the start date of the query (in this example, 2006-01-08). Failing to do this would mean that you'd miss the total for your start date—if there were no Start or Finish event on 2006-01-08, in this example.

 The ROW_NUMBER() function is a new, long-awaited feature in SQL Server 2005.
I then reflected on this type of query in light of the new T-SQL enhancements of SQL Server 2005. Listing 2 is really two queries that have been nicely put together into one result set. Each one had to go to the Totals view twice—once for each amount. I wondered if you could get the start and finish amounts without having to tap the view twice. I thought about the new ROW_NUMBER() function (a new, long-awaited feature in SQL Server 2005). This gives you a running number based on a given sort order. Armed with this, you want the first and the last. The first is easy—it's 1. The last requires some work because it's equal to the number of rows involved.

Then it dawned on me. There's nothing to stop you from using the ROW_NUMBER() function twice in the same SELECT. You can also give each one of them different sort criteria. What if you were to give them the same sort criteria, but making one ascending, and the other descending? That would mean that you'd have ROW_NUMBER() = 1 for the first row when ascending, and ROW_NUMBER() = 1 for the last row when descending. Check out Listing 3 for the solution.

You need the derived table—x—because you can't use ROW_NUMBER() in the WHERE clause. I used the MIN() functions in concert with the CASE expressions to extract the correct Amount values. MAX() or SUM() would accomplish the same thing, because there's only one row where RowNumber1 = 1 or RowNumber2 = 1. I was very pleased to see that the relative query cost for this approach is 34% vs. 66 % for Listing 2—a 2:1 improvement.

If you examine Listing 3 closely, you'll see that the MIN(CASE…) is essentially an unpivot without explicitly using UNPIVOT. I tried the explicit UNPIVOT, but the relative query cost was slightly worse than Listing 3. Also, because of certain restrictions with the UNPIVOT feature, I had to use an extra derived table, making the code a little more difficult to read. Here's one more challenge for you: Try to find the days where the largest increment/decrement occurred. Since you're interested in the changes and not the totals, you don't need to use the Totals view. Your first impulse might be to go directly to the Deltas table. However, keep in mind that several increments can occur either starting on the same day or ending on the same day. For this job, you'll need to use your own CTEs. Go ahead, and while you do that, I'll head to the kitchen and top up my coffee.

Okay, I'm back. Let's see how you did. Compare your code to what I did in Listing 4. The CTEs begin life in the same manner as when you built the Totals view. Indeed, CTEs x and y are identical. You next take CTE y and do the same sort of bookend query that I talked about in Listing 3. (Thought that you were finished with that one, huh?) Use ROW_NUMBER() = 1 to define the bookends for both sort orders and—voilá!—you're there.

Before I go, I'll leave you with some homework. Find the period(s) of time when there was nothing on the books. Hint: The total goes to zero on a Finish Event and to non-zero on the following Start event.

Tom Moreau has been using SQL Server since 1993, and loving it. He's a consultant based in Toronto, specializing in database design, implementation, and administration using SQL Server technology. Tom has written over 100 articles on SQL Server since 1997. He's a former monthly columnist, having written "Dr. Tom�s Workshop" for SQL Server Professional. Tom became an MVP for SQL Server in 2001.