查找用于将财政季度结果与上一年进行比较的查询

Question

使用 MS SQL Server 2014 和 AdventureWorks2012 DB。

目前正在尝试找出正确的查询以得出一个结果，该结果显示同一行的财政季度中每个销售人员的总销售额，与上一年的财政季度进行比较。我应该在结果中的列是;

• 姓氏
• 销售人员ID
• 财年
• 财政季度
• 财季销售额
• 上一财年同一财政季度的销售额
• 两个期间之间的收入变化
• 两个期间之间的收入变化百分比

我下面的查询不包括最后 2 列，因为我不确定如何获得它们？但是，没有它们的查询似乎也不正确，因为它没有得到所需的结果。

我尝试在没有soh.OrderDate的情况下运行此代码GROUP BY这是我之前为另一个相关查询所做的，但是这返回了以下错误，因此我已将其包含在GROUP BY中;

列"Sales.SalesOrderHeader.OrderDate"在选择列表中无效，因为它未包含在聚合函数或 GROUP BY 子句中。

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;

当前结果，为每个销售人员获得不同的排列，但是我希望每个销售人员有 4 个结果，每个 FQ 有 1 个结果。

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

期望的结果：

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

Answer 1

首先，您应该将数据分成两个表中，每个表对应一个，计算每个表的 FY 和 FQ。

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' 

然后加入他们并在没有 orderDate 的情况下进行分组，否则它将为每个日期创建一行：

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

Answer 2

使用条件聚合：

SELECT pp.LastName, sp.BusinessEntityID AS SalesPersonID,
v.FY, v.FQ,
SUM(CASE WHEN FY = 2012 THEN soh.SubTotal END) as FY_prev,
SUM(CASE WHEN FY = 2013 THEN soh.SubTotal END) as FY_curr,
( SUM(CASE WHEN FY = 2013 THEN soh.SubTotal END) /
SUM(CASE WHEN FY = 2012 THEN soh.SubTotal END)
) - 1 as percent
FROM Sales.SalesPerson sp INNER JOIN
Person.Person pp
ON sp.BusinessEntityID = pp.BusinessEntityID INNER JOIN
Sales.SalesOrderHeader soh
ON sp.BusinessEntityID = soh.SalesPersonID CROSS APPLY
(VALUES ((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),
(CASE WHEN MONTH(soh.OrderDate) < 7 THEN YEAR(soh.OrderDate)
ELSE YEAR(soh.OrderDate) + 1
END)
)
) v(FQ, FY)
WHERE FY IN (2013, 2012)
GROUP BY pp.LastName, sp.BusinessEntityID, v.FY, v.FQ;

这使用CROSS APPLY来创建价值。 这也可以通过 CTE 或子查询来实现。