Better optimization of the query

Question

Is there any way that I can improve this query?

SELECT  FullName ,
        LastMonthAmount ,
        ThisMonthAmount ,
        ThisMonthCount
FROM    ( SELECT    SUM([Sales].saleAmount) AS LastMonthAmount ,
                    Sales.cID
          FROM      dbo.[Sales]
                    JOIN Consultants ON Consultants.conID = Sales.cID
          WHERE     YEAR(Sales.salesDate) = YEAR(GETDATE())
                    AND MONTH(Sales.salesDate) = MONTH(GETDATE()) - 1
          GROUP BY  Sales.cID ,
                    Consultants.cName
        ) AS LastMonthAmount
        INNER JOIN ( SELECT SUM([Sales].saleAmount) AS ThisMonthAmount ,
                            Sales.cID ,
                            COUNT(Sales.salesID) AS ThisMonthCount ,
                            Consultants.cName AS FullName
                     FROM   dbo.[Sales]
                            JOIN Consultants ON Consultants.conID = Sales.cID
                     WHERE  YEAR(Sales.salesDate) = YEAR(GETDATE())
                            AND MONTH(Sales.salesDate) = MONTH(GETDATE())
                     GROUP BY Sales.cID ,
                            Consultants.cName
                   ) AS CurrentMonth ON LastMonthAmount.cID = CurrentMonth.cID;

Answer 1

Try "With Clause" if you are using SQL server, if not I could provide a self join solution would work as well.

http://sqlfiddle.com/#!3/752a6/26

WITH monthly_report(total_sales, total_count, month_date, cName) AS
(SELECT SUM(saleAmount),
        COUNT(DISTINCT sales),
        DATEADD(MONTH, DATEDIFF(MONTH, 0, salesDate), 0),
        cName
FROM sales s
INNER JOIN Consultants c
ON c.cID = s.cID
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, salesDate), 0),
         cName) 
SELECT thisMonth.cName, thisMonth.total_sales,lastMonth.total_sales, lastMonth.total_count
FROM monthly_report thisMonth
  LEFT JOIN monthly_report lastMonth
    ON lastMonth.month_date = DATEADD(MONTH, -1, thisMonth.month_date)
AND thisMonth.cName = lastMonth.cName
WHERE thisMonth.month_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETUTCDATE()), 0);

Basically we are using the sales date and get the first date of the month, then we can simply group by them using consultants and each first month of the data for calculating sales total and sales count.

Answer 2

If you really need 1 record to contain both a column for LastMonth SUM and CurrentMonth SUM you can use the double query for separate selection. Note that you only retrieve Consultants that have sales in LastMonth to see CurrentMonth and vice versa. Use FULL OUTER JOIN if you also want to see figures for Consultants that were not active in both periods.

Selecting >= LastMonth and group by Month will result in a record per month which will be a lot faster.

SELECT    
          Sales.cID, Consultants.cName, MONTH(Sales.salesDate),
          SUM([Sales].saleAmount) AS MonthAmount,
          COUNT(Sales.salesID) AS MonthCount 
FROM      dbo.[Sales]
JOIN      Consultants ON Consultants.conID = Sales.cID
WHERE     
          YEAR(Sales.salesDate) = YEAR(GETDATE())
          AND MONTH(Sales.salesDate) >= MONTH(GETDATE()) - 1
GROUP BY  Sales.cID,
          Consultants.cName,
          MONTH(Sales.salesDate)

If you don't want separate records per month this will probably still be faster then your original if you have millions/billions of records.

DECLARE @PreviousMonth int = MONTH(GETDATE()) - 1
DECLARE @CurrentMonth int = MONTH(GETDATE())
DECLARE @CurrentYear int = YEAR(GETDATE())

SELECT    
          Sales.cID, Consultants.cName, 
          SUM(
              CASE 
                  WHEN MONTH(Sales.salesDate) = @PreviousMonth
                  THEN [Sales].saleAmount
                  ELSE 0
              END
          ) AS LastMonthAmount, 
          SUM(
              CASE 
                  WHEN MONTH(Sales.salesDate) = @CurrentMonth
                  THEN [Sales].saleAmount
                  ELSE 0
              END
          ) AS CurrentMonthAmount, 
          SUM(
              CASE 
                  WHEN MONTH(Sales.salesDate) = @CurrentMonth
                  THEN 1
                  ELSE 0
              END
          ) AS CurrentMonthCount
FROM      dbo.[Sales]
JOIN      Consultants ON Consultants.conID = Sales.cID
WHERE     
          YEAR(Sales.salesDate) = @CurrentYear
          AND MONTH(Sales.salesDate) >= @PreviousMonth
GROUP BY  Sales.cID,
          Consultants.cName

What you also might have missed is that you also want it to work for January? The overview will not show December as LastMonth, since MONTH(GETDATE()) - 1 will result in Month 0. With the original INNER JOIN you won't see data for December and January. With a FULL OUTER JOIN you will only see data for Current Month January and nothing for Last Month December. If you want an overview with both January and December, you shouldn't use date parts, but create dates.

//Using DateFromParts introduced in sql server2012
DECLARE @ThisMonthStart date = 
    DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
DECLARE @LastMonthStart = DATEADD(mm, @ThisMonthStart, -1)
//Now check between @LastMonthStart and @ThisMonthStart for LastMonth
//and check > @ThisMonthStart for ThisMonth
//or take these Years and Months

Answer 3

Something like this should work:

DECLARE @PrevMonth date, @CurrMonth date, @NextMonth date

set @CurrMonth = DATEADD(month, DATEDIFF(month, 0, getdate()), 0)
set @NextMonth = dateadd(month, 1, @CurrMonth)
set @PrevMonth = dateadd(month, -1, @CurrMonth)

SELECT
  SUM(case when salesDate >= @CurrMonth and salesDate < @NextMonth then saleAmount else 0 end) AS ThisMonthAmount,
  SUM(case when salesDate >= @PrevMonth and salesDate < @CurrMonth then saleAmount else 0 end) AS PrevMonthAmount,
  SUM(case when salesDate >= @CurrMonth and salesDate < @NextMonth then 1 else 0 end) AS ThisMonthCount,
  Sales.cID
  Consultants.cName AS FullName
FROM   
  dbo.Sales
  JOIN Consultants ON Consultants.conID = Sales.cID
WHERE  
  Sales.salesDate >= @PrevMonth and
  Sales.salesDate < @NextMonth
GROUP BY 
  Sales.cID,
  Consultants.cName

The case statements inside sum are used to calculate only the correct values to that column, and treat values outside the range as zeros.

Corrected the way date criteria should be handled instead of year() and month() function.