John Tipton John Tipton - 4 years ago 124
SQL Question

sql - min of 9 weeks max of 15 weeks average of 16 weeks

This is what I desire for my query


  • average for 16 wks

  • min 9 wks

  • max 15 wks

  • increasing month NOT alphabetic



enter image description here

And my query so far looks like
enter image description here

My code is

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]));


Thank you so much in advance!

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

Answer Source

Consider a join of two crosstab queries.

CrossTab1 Query (specify the column order in PIVOT clause)

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 Query (save as separate query)

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 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');

Final Query (joining both crosstabs)

SELECT t1.*, t2.*
FROM CrossTab1 t1 
INNER JOIN CrossTab2 t2
ON t1.ItemCode = t2.ItemCode
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download