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

Question

这就是我所想要的

• 平均16周
• 最少9周
• 最多15周
• 增加月份而不是字母

​

​

到目前为止，我的查询如下

​

我的代码是

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

Answer 1

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

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与分类度量列合并的聚合查询：

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