Summary report grouped on multiple date ranges

Question

I need to create a sales & commission report

Basically it goes (please forgive the blatent craziness of the SaleDate table, but I'm simplifying the business logic, and in reality it actually makes sense to have it this way)

SELECT agentName, 
       SUM(sales.Amount) AS Gross,
       SUM(sales.Amount * sales.Commission) AS Commission
FROM agent
     INNER JOIN sales ON agent.agentId = sales.agentId
WHERE sales.saleId IN (SELECT saleId FROM saleDate WHERE saleDate.myDate BETWEEN @minDate AND @maxDate)
GROUP BY agentName

So this query works absolutely fine. The problem occurs when I need to add a second date range.

IE, where they want to compare 2007 sales & 2008 sales side by side.

I currently have basically the same query, but I've added aliases to the sales table and added another one

SELECT agentName, 
       SUM(sales1.Amount) AS Gross1,
       SUM(sales1.Amount * sales1.Commission) AS Commission1,
       SUM(sales2.Amount) AS Gross2,
       SUM(sales2.Amount * sales2.Commission) AS Commission2,
       SUM(sales3.Amount) AS Gross3,
       SUM(sales3.Amount * sales3.Commission) AS Commission3
FROM agent
     INNER JOIN sales1 ON agent.agentId = sales1.agentId
     INNER JOIN sales2 ON agent.agentId = sales2.agentId
     INNER JOIN sales3 ON agent.agentId = sales3.agentId
WHERE sales1.saleId IN (SELECT saleId FROM saleDate WHERE saleDate.myDate BETWEEN @minDate1 AND @maxDate1) OR
      sales2.saleId IN (SELECT saleId FROM saleDate WHERE saleDate.myDate BETWEEN @minDate2 AND @maxDate2) OR
      sales3.saleId IN (SELECT saleId FROM saleDate WHERE saleDate.myDate BETWEEN @minDate3 AND @maxDate3) 
GROUP BY agentName

This query however is taking forever (over 20 minutes before I cancelled it), the original took less than a second and if I only use two groups it takes 9 seconds.

Any ideas on how to improve this performance?

I'm willing to change the design of this query.

Answer 1

If your first query works fine, why not just UNION ALL it with the new dates subbed into the 2nd version? Yes, you won't get a pivoted output, but that's easy enough to fix with application layer presentational code.

Another option would be to use CASE.

SELECT agentName, 
SUM (CASE WHEN saleDate.myDate BETWEEN @minDate1 AND @maxDate1 THEN sales.Amount ELSE 0 END) AS Gross1, 
SUM (CASE WHEN saleDate.myDate BETWEEN @minDate1 AND @maxDate1 THEN sales.Commission ELSE 0 END) AS Commission1,   
SUM (CASE WHEN saleDate.myDate BETWEEN @minDate2 AND @maxDate2 THEN sales.Amount ELSE 0 END) AS Gross2, 
SUM (CASE WHEN saleDate.myDate BETWEEN @minDate2 AND @maxDate2 THEN sales.Commission ELSE 0 END) AS Commission2
FROM agent
INNER JOIN sales ON agent.agentId = sales.agentId
GROUP BY agentName

Answer 2

Check your SELECT block, it reads:

SELECT agentName,
SUM(sales1.Amount) AS Gross1,
SUM(sales1.Amount * sales1.Commission) AS Commission1,
SUM(sales2.Amount) AS Gross2,
SUM(sales2.Amount * sales2.Commission) AS Commission2

and you are joining on a SALES3 table whose values are not present in the SELECT clause. This confuses SQL Server. Remove the INNER JOIN on Sales3 and it's OR piece in the WHERE block and it'll work.

OR

Add those items to the SELECT block.

Answer 3

I would strongly suggest splitting this into 3 independent queries, and then merge the results via UNION

Answer 4

This is just a shot in the dark w/o any table info (size, schema, indexes), but try moving the date filter below the join operators:

SELECT agentName, 
           SUM(s1.Amount) AS Gross1,
           SUM(s1.Amount * s1.Commission) AS Commission1,
           SUM(s2.Amount) AS Gross2,
           SUM(s2.Amount * s2.Commission) AS Commission2
    FROM agent
    INNER JOIN (
         SELECT sales1.agentID, Commission, Amount
         FROM sales1 WHERE saleId IN (
               SELECT saleId FROM saleDate 
               WHERE saleDate.myDate BETWEEN @minDate1 AND @maxDate1)
         ) s1 ON agent.agentId = s1.agentId 
    INNER JOIN (
         SELECT sales2.agentID, Commission, Amount
         FROM sales2 WHERE saleId IN (
                SELECT saleId FROM saleDate
                WHERE saleDate.myDate BETWEEN @minDate1 AND @maxDate1)
         ) s2 ON agent.agentId = s2.agentId;
--    INNER JOIN (
--        SELECT sales3.agentID 
--         FROM sales3 WHERE saleId IN (
--              SELECT saleId FROM saleDate 
--              WHERE saleDate.myDate BETWEEN @minDate1 AND @maxDate1)
--         ) s3 ON agent.agentId = s3.agentId;