sql-最少9周，最多15周，平均16周

问题描述

这就是我要查询的内容

• 16周平均水平
• 最少9周
• 最多15周
• 不按字母顺序增加月份

到目前为止，我的查询看起来像

And my query so far looks like

我的代码是

TRANSFORM SUM(Detail.Quantity)
SELECT Detail.ItemCode
FROM Detail INNER JOIN Header ON Detail.SalesOrderNo = Header.SalesOrderNo
WHERE Header.OrderDate>=dateadd("m",-4,Date())
GROUP BY Detail.ItemCode
PIVOT MonthName(Month([Header.OrderDate]));

非常感谢您！

如果有人能回答为什么2017年2月1日开始显示十月"，我们将不胜感激.而我的代码中有WHERE Header.OrderDate>=dateadd("m",-4,Date())?

And It'd be appreciated If anyone can answer why 'October' is showing as of 2/1/2017? while my code has WHERE Header.OrderDate>=dateadd("m",-4,Date())?

推荐答案

考虑两个交叉表查询的联接.

Consider a join of two crosstab queries.

CrossTab1 查询(在PIVOT子句中指定列顺序)

TRANSFORM SUM(Detail.Quantity)
SELECT Detail.ItemCode
FROM Detail INNER JOIN Header ON Detail.SalesOrderNo = Header.SalesOrderNo
WHERE Header.OrderDate>=dateadd("m",-4,Date())
GROUP BY Detail.ItemCode
PIVOT MonthName(Month([Header.OrderDate]))
  IN ('November', 'December', 'January', 'February');

UnionAggQ 查询(另存为查询)

下一个交叉表需要另一个查询作为源，特别是 ItemCode 与分类 Metric 列的聚合的并集查询:

Next crosstab needs another query as the source, specifically a union query of aggregates by ItemCode with categorical Metric column:

SELECT Detail.ItemCode,
       'AVG 16 WEEKS' AS Metric,
       AVG(Detail.Quantity) AS AggDetailQty
FROM Detail INNER JOIN Header ON Detail.SalesOrderNo = Header.SalesOrderNo
WHERE Header.OrderDate >= DATEADD("ww",-16, Date())
GROUP BY Detail.ItemCode

UNION ALL

SELECT Detail.ItemCode,
       'MIN 9 WEEKS' AS Metric,
       MIN(Detail.Quantity) AS AggDetailQty 
FROM Detail INNER JOIN Header ON Detail.SalesOrderNo = Header.SalesOrderNo
WHERE Header.OrderDate >= DATEADD("ww",-9, Date())
GROUP BY Detail.ItemCode

UNION ALL

SELECT Detail.ItemCode,
       'MAX 15 WEEKS' AS Metric,
       MAX(Detail.Quantity) AS AggDetailQty
FROM Detail INNER JOIN Header ON Detail.SalesOrderNo = Header.SalesOrderNo
WHERE Header.OrderDate >= DATEADD("ww",-15, Date())
GROUP BY Detail.ItemCode

Crosstab2 查询

TRANSFORM SUM(q.AggDetailQty)
SELECT q.ItemCode
FROM UnionAggQuery q
GROUP BY q.ItemCode
PIVOT q.Type IN ('AVG 16 WEEKS', 'MIN 9 WEEKS', 'MAX 15 WEEKS');

最终查询(同时连接两个交叉表)

SELECT t1.*, t2.*
FROM CrossTab1 t1 
INNER JOIN CrossTab2 t2
ON t1.ItemCode = t2.ItemCode

这篇关于sql-最少9周，最多15周，平均16周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！