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
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