John Tipton John Tipton - 4 years ago 77
SQL Question

sql - need clarification "q.type" on crosstab/aggregates for average/min/max

It's a follow-up question for my previous question:sql - min of 9 weeks max of 15 weeks average of 16 weeks

I'm getting "Enter parameter value of

q.Type
" when I execute parfait's awesome answer.

Can someone explain why and how can I substitute/specify it?

I don't want to manually enter value all the time..

The below is his code answer:

Crosstab2 Query

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 (joining both crosstabs)

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


Final Query

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

Answer Source

Try this:

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');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download