这是我上一个问题的后续问题:sql - 最少 9 周 最多 15 周 平均 16 周
当我执行冻糕的真棒答案时,我得到"输入q.Type
的参数值"。
有人可以解释为什么以及如何替换/指定它吗?
我不想一直手动输入值。
以下是他的暗语答案:
交叉表2 查询
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');
UnionAggQ Query(连接两个交叉表)
下一个交叉表需要另一个查询作为源,特别是按 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
最终查询
SELECT t1.*, t2.*
FROM CrossTab1 t1
INNER JOIN CrossTab2 t2
ON t1.ItemCode = t2.ItemCode
试试这个:
TRANSFORM SUM(q.AggDetailQty)
SELECT q.ItemCode
FROM UnionAggQuery q
GROUP BY q.ItemCode
PIVOT q.Metric IN ('AVG 16 WEEKS', 'MIN 9 WEEKS', 'MAX 15 WEEKS');