sqlsql-serverdatabaset-sqladventureworks

Find the query for comparing fiscal quarter results with the previous year


Using MS SQL Server 2014 and AdventureWorks2012 DB.

Currently trying to work out the correct query to reach a result which displays total sales value for each sales person for fiscal quarters with on the same row, a comparison to the fiscal quarter of the previous year. The columns I should have in the result are;

My query below does not include the final 2 columns as I am unsure how to get them? However the query without them also seems incorrect as it is not getting the desired result.

I have tried to run this code without having soh.OrderDate in the GROUP BY which was something I did previously for another related query however this returns the following error hence I have included it back in the GROUP BY;

Column 'Sales.SalesOrderHeader.OrderDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


SELECT pp.LastName, sp.BusinessEntityID AS SalesPersonID,
    CASE
      WHEN soh.OrderDate BETWEEN '2013-07-01' AND '2014-06-30' 
        THEN '2013'
    END AS FY,
    CASE 
      WHEN MONTH(soh.OrderDate) BETWEEN 7 AND 9 THEN '1'
      WHEN MONTH(soh.OrderDate) BETWEEN 10 AND 12 THEN '2'
      WHEN MONTH(soh.OrderDate) BETWEEN 1 AND 3 THEN '3'
      WHEN MONTH(soh.OrderDate) BETWEEN 4 AND 6 THEN '4'
    END AS FQ,
    CASE 
      WHEN soh.OrderDate BETWEEN '2013-07-01' AND '2014-06-30' 
        THEN 
          SUM(CASE
        WHEN MONTH(soh.OrderDate) BETWEEN 7 AND 9 THEN soh.SubTotal
        WHEN MONTH(soh.OrderDate) BETWEEN 10 AND 12 THEN soh.SubTotal
            WHEN MONTH(soh.OrderDate) BETWEEN 1 AND 3 THEN soh.SubTotal
        WHEN MONTH(soh.OrderDate) BETWEEN 4 AND 6 THEN soh.SubTotal
          END)
    END AS FQSales, 
    CASE 
      WHEN soh.OrderDate BETWEEN '2012-07-01' AND '2013-06-30' 
        THEN 
          SUM(CASE
        WHEN MONTH(soh.OrderDate) BETWEEN 7 AND 9 THEN soh.SubTotal
        WHEN MONTH(soh.OrderDate) BETWEEN 10 AND 12 THEN soh.SubTotal
        WHEN MONTH(soh.OrderDate) BETWEEN 1 AND 3 THEN soh.SubTotal
        WHEN MONTH(soh.OrderDate) BETWEEN 4 AND 6 THEN soh.SubTotal
          END)
    END AS SalesSameLastFQ
FROM Sales.SalesPerson sp
INNER JOIN Person.Person pp 
    ON sp.BusinessEntityID = pp.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader soh 
    ON sp.BusinessEntityID = soh.SalesPersonID
GROUP BY pp.LastName, sp.BusinessEntityID, soh.OrderDate;

Current result, getting different permutations for each Sales Person however I expect to have 4 results for each sales person, 1 for each FQ.

LastName     SalesPersonID     FY      FQ   FQSales  SalesSameLastYr
------------------------------------------------------------------
Alberts          283          NULL     1   NULL      NULL
Alberts          283          NULL     1    NULL     32344.342
Alberts          283          NULL     3   NULL      NULL
Alberts          283          2013     2   342432      NULL
Alberts          283          NULL     4    NULL     32344.342
Alberts          283          NULL     3   NULL      NULL
Alberts          283          NULL     4    NULL     32344.342
Alberts          283          2013     2   436346      NULL

Desired result:

LastName  SalesPersID   FY   FQ FQSales SalesSameLastYr Change Change%
-----------------------------------------------------------------------
Alberts      283       2013   1  2000     1900           100      5
Alberts      283       2013   2  2200     2000           200      10
Alberts      283       2013   3  2000     2100          -100     -5
Alberts      283       2013   4  3000     2850           150      5
Mathews      291       2013   1  2000     1900           100      5
Mathews      291       2013   2  2200     2000           200      10
Mathews      291       2013   3  2000     2100          -100     -5
Mathews      291       2013   4  3000     2850           150      5

Solution

  • You first should separate the data in two tables one for each year, culculating the FY and FQ for each one.:

    SELECT pp.LastName, sp.BusinessEntityID AS SalesPersonID,'2013' as FY,
        CASE 
          WHEN MONTH(soh.OrderDate) BETWEEN 7 AND 9 THEN '1'
          WHEN MONTH(soh.OrderDate) BETWEEN 10 AND 12 THEN '2'
          WHEN MONTH(soh.OrderDate) BETWEEN 1 AND 3 THEN '3'
          WHEN MONTH(soh.OrderDate) BETWEEN 4 AND 6 THEN '4'
        END AS FQ,
        soh.SubTotal
    into #tmpCurrentYear
    FROM Sales.SalesPerson sp
    INNER JOIN Person.Person pp ON sp.BusinessEntityID = pp.BusinessEntityID
    INNER JOIN Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
    where soh.OrderDate between '2013-07-01' AND '2014-06-30'
    
    SELECT pp.LastName, sp.BusinessEntityID AS SalesPersonID,'2012' as FY,
        CASE 
          WHEN MONTH(soh.OrderDate) BETWEEN 7 AND 9 THEN '1'
          WHEN MONTH(soh.OrderDate) BETWEEN 10 AND 12 THEN '2'
          WHEN MONTH(soh.OrderDate) BETWEEN 1 AND 3 THEN '3'
          WHEN MONTH(soh.OrderDate) BETWEEN 4 AND 6 THEN '4'
        END AS FQ,
        soh.SubTotal
    into #tmpLastYear
    FROM Sales.SalesPerson sp
    INNER JOIN Person.Person pp ON sp.BusinessEntityID = pp.BusinessEntityID
    INNER JOIN Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
    where soh.OrderDate between '2012-07-01' AND '2013-06-30' 
    

    And then join them and do the group by without the orderDate or it will create one row for each date:

    select t.LastName, t.SalesPersonID,t.FY,t.FQ, sum(t.SubTotal) as FQSales,         
    sum(ly.SubTotal) as SalesSameLastFQ, sum(t.SubTotal)-sum(ly.SubTotal) as Change, 
          (sum(t.SubTotal)-sum(ly.SubTotal))/(sum(t.SubTotal)) as ChangePercentage
    from #tmpCurrentYear as t
        INNER JOIN  #tmpLastYear as ly ON t.LastName = ly.LastName and t.BusinessEntityID = ly.BusinessEntityID and t.FQ = ly.FQ
    group by t.LastName, t.SalesPersonID,t.FY,t.FQ