Aggregation based on Date

Imagine you have Sales database, we'll use AdventureWorks, and you need to return the number of sales by product in the last 30, 90, and 180 days. How would you do this? In the past I might have used temp tables, table variables, or a union in order to get the the numbers for each date range. This would not be, in my opinion, the optimal way to retrieve this data. You could just return all the data with order dates and use Excel, SSRS, or another reporting tool to pivot the data, but once again this is probably not the optimal way to return the data. So, what do I think is the best way to do this? I do this in a single select filtering on the largest date range using CASE to return the data for the smaller date ranges. Here is the code, based on AdventureWorks (I use 7/1/2004 because that is the most recent order date in my copy of AdventureWorks):

SELECT
D.ProductId,
COUNT(H.SalesOrderID) AS sales_for_180,
SUM(D.ORderQty) AS no_products_sold_180,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -90, '7/31/2004' ) THEN 1
ELSE 0
END) AS sales_for_90,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -90, '7/31/2004' ) THEN D.OrderQty
ELSE 0
END) AS no_products_sold_for_90,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -30, '7/31/2004' ) THEN 1
ELSE 0
END) AS sales_for_30,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -30, '7/31/2004' ) THEN D.OrderQty
ELSE 0
END) AS no_products_sold_for_30
FROM
Sales.SalesOrderHeader H JOIN
Sales.SalesOrderDetail D ON
H.SalesOrderID = D.SalesOrderID
WHERE
H.OrderDate < '7/31/2004' AND
H.OrderDate >= DATEADD(DAY, -180, '7/31/2004')
GROUP BY
D.ProductId

This will return the data you asked for in a single pass and will do an index seek if you have an index on OrderDate. All in all an efficient way to return the desired data. You may have learned this a long time ago, but it is something I recently picked up when answering a question on SQLServerCentral, so I know not everyone knows this. This technique works whenever you need aggregates based on some sort of partition. It could be a breakdown of products within a product category as well.

Ghost of SQL Server 2005 Database Mail Reply To - Fix

If you have been following my posts at all, you know I have had some issues with Database Mail (1, 2, 3, 4). Well the first one has been fixed, I have not tested the fix yet, by Microsoft in Cumulative Update Package 8 for Service Pack 2. The article for that is here, http://support.microsoft.com/kb/953841/en-us. Microsoft never updated my Connect post, where they said it would not be fixed unless a QFE was requested, but I'm not complaining.

Now I need to try it out on my personal SQL Server.

SSIS Webinars, tablediff, and Free Zoom Tool

I had the opportunity to attend 3 of 5 free webinars on SSIS presented by Brian Knight of Pragmatic Works. The 2 I chose not to participate in were introductory in nature, and I have some experience with SSIS. I attended Administering and Deploying SSIS Packages, Performance Tuning in SSIS, and SSIS for the DBA. I enjoyed each of the webinars and picked up some good tips. I enjoy taking any opportunities, particularly free ones, to expand my toolkit and knowledge base. I appreciate Brain and co-workers taking the time to present the webinars as I know that there had to be a lot of time and effort put into doing them. I hope it pays off for them in their business as well. I believe that the goodwill it will. Check out the Pragmatic Works web site as they offer some very good custom SSIS tasks for free! They also offer some other commercial product as well.

I actually used something I learned in one of the Webinars today, Webinar SSIS for the DBA,. I used an Execute SQL Task to populate an object variable with a list of schemas and tables and then used a For Each Loop Task to loop through the result set and use the Execute Process Task to run the tablediff utility Microsoft has included with SQL Server 2005 (I read about it in Benjamin Wright-Jones' blog earlier today). It is a command line utility that compares 1 set of tables (source and destination) and I wanted to compare all the tables in my database to my development database so I needed the loop. Tablediff compares the schemas and if they are different will return an error, "can not compare the table x to table y because the schemas are different". If the schemas are the same it will, depending on the parameters you supply, show any differences in the data and prepare a T-SQL scipt to synchronize the data. I actually was only interested in comparing the schemas and, not having read all the documentation, was hoping it would report those differences as well. Maybe in a later version of SQL Server a schema comparison tool will be included (sorry Red Gate). It was still a good exercise, and knowing about the tablediff utility will probably come in handy later.

One last thing, Brian used a nice free tool to zoom and markup during his presentation. It is called ZoomIt and is by Sysinternals (now part of Microsoft). I would recommend it to anyone who give presentations as it really helped us see what Brian was doing.