Problem

A post was recently added to one of the forums that I monitor so that I can help users in the SQL Server community with their issues. In this problem/solution blog post I am referencing a user trying aggregate Category and Subcategory Sales values. Not to tricky so far right? One of the other requirements was that he didn’t want to show any results where the total of the subcategory sales were less than a threshold of 20K. I personally wouldn’t want a report to filter out this data, but hey… the users get what they want right? The report was written in Reporting Services, but at this time, I am unsure of where the data is coming from. I will use SQL Server 2008 for this blog. I am also assuming that he had a grouping of Category and another for SubCategory. Each of which containing a totals row.

Solution

I gave one possible solution of not returning the results at the query level for the subcategory with less than the total sales amount of the threshold. His response to this was that he wasn’t sure how to do this so, I thought what a better way to answer his question than to start a new blog series for these problem/solution scenarios. It’s not a new concept really, but hey… I think it fits well here too! So, without further ado… let’s get crackalackin…

To set this up, we need some sample data to play with. As such, I have created a rough sample of results. Normally the data would be a little more complex than this, but this should give anyone else out there a pretty good idea of how one could limit the results for a certain threshold. Just keep in mind that this is only one way that it could be done using SQL 2005 and up since I am using a CTE. Using a SQL 2000 or lower db, you’ll need to implement it differently. Perhaps, maybe using a derived table and joining it to the results or loading a table variable with categories or subcategories and then doing a where IN clause. There are many ways to implement this.

At any rate, back to the solution! I have included a script below that could be used to create sample data and query the results. In my solution, I am using a table variable to make it usable by the masses. However, in a real world solution, this data will more than likely come from a physical table. Also, you will not need to insert any data, but has been included for your viewing pleasure. Well… you know what I mean. J The key part of this solution is in how I used a CTE to generate a Total Sales result set. Then I join that result set back into my results (source table(s)) with a filter in the where clause to limit the values to only those within the acceptable threshold.

Script 1: Complete Script With Results

--Create table variable to hold our results

DECLARE @Results TABLE

      (Category VARCHAR(10)

      , SubCategory VARCHAR(10)

      , OrderNumber INT

      , SalesAmount MONEY)

--Insert some test data

INSERT INTO @Results

      (Category

      , SubCategory

      , OrderNumber

      , SalesAmount)

VALUES

      ('Coats','Winter',500, 10000.00)

      , ('Coats','Winter',501, 12000.00)

      , ('Coats','Winter',550, 15000.00)

      , ('Coats','Spring',506, 2341.00)

      , ('Coats','Ski',570, 1234.00)

      , ('Coats','Spring',570, 34653.00)

      , ('Coats','Spring',575, 45.00)

      , ('Coats','Ski',456, 9900.00)

      , ('Coats','Winter',789, 123.00)

      , ('Coats','Ski',987, 17000.00)

      , ('Coats','Winter',897, 1212.00)

      , ('Coats','Winter',879, 123.00)

      , ('Coats','Fall',999, 1500.00)

      , ('Coats','Fall',999, 3500.00)

      , ('Coats','Fall',999, 2000.00)

/*Now let's create a query that returns only the results

where the sum of the sales is > our threshold of 20K */

DECLARE @SalesThreshold MONEY = 20000.00

BEGIN

WITH cte_TotalSales

AS

(

      SELECT

            [Category]

            , [SubCategory]

            , SUM([SalesAmount]) AS [TotalSales]

      FROM

            @Results

      GROUP BY

            [Category], [SubCategory]

)

SELECT

      r.[Category]

      , r.[SubCategory]

      , r.[OrderNumber]

      , r.[SalesAmount]

FROM

      @Results r

      JOIN cte_TotalSales cte ON r.[Category] = cte.[Category]

      AND r.[SubCategory] = cte.[SubCategory]

WHERE

      cte.[TotalSales] >= @SalesThreshold

--no coats in the Fall subcategory should be returned to the report

END

Results of running the above script will return all of the results that we want. Notice that we did not return the results for Fall Coats? This is because the total of all the sales are less than the threshold of $20,000.

Figure 1: Sample Results

With the results the way we want, now we could create a quick report that would allow us to show aggregated values at each of the group levels and provide an interactive drill down into the details. While removing any values that we don’t want to display. Figure 2 is a quick sample report that I threw together to show you what a report may look like.

Figure 2: Sample Report Filtering Subcategory Not Meeting Threshold

I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs or find me at any of the below methods.