weighted-quarters-to-year.sql

SELECT     
	year.PartNum,
	SUM((ISNULL(q1.Hits, 0) + ISNULL(q2.Hits, 0) + ISNULL(q3.Hits, 0) + ISNULL(q4.Hits, 0))) AS HitsDEBUG,
	SUM((ISNULL(q1.Hits, 0) + ISNULL(q2.Hits, 0) + ISNULL(q3.Hits, 0) + ISNULL(q4.Hits, 0)) / 4) AS Hits,
	SUM((ISNULL(q1.QuarterTotal, 0) + ISNULL(q2.QuarterTotal, 0) + ISNULL(q3.QuarterTotal, 0) + ISNULL(q4.QuarterTotal, 0)) / 4) AS QuarterTotal,
	SUM((ISNULL(q1.MonthAvg, 0) + ISNULL(q2.MonthAvg, 0) + ISNULL(q3.MonthAvg, 0) + ISNULL(q4.MonthAvg, 0)) / 4) AS MonthAvg,
	SUM((ISNULL(q1.MonthStd, 0) + ISNULL(q2.MonthStd, 0) + ISNULL(q3.MonthStd, 0) + ISNULL(q4.MonthStd, 0)) / 4) AS MonthStd,
	SUM((ISNULL(q1.PartAvg, 0) + ISNULL(q2.PartAvg, 0) + ISNULL(q3.PartAvg, 0) + ISNULL(q4.PartAvg, 0)) / 4) AS PartAvg,
	SUM((ISNULL(q1.PartStd, 0) + ISNULL(q2.PartStd, 0) + ISNULL(q3.PartStd, 0) + ISNULL(q4.PartStd, 0)) / 4) AS PartStd,
	MAX(year.MinShipQty) AS MinShipQty, 
	MAX(year.MaxShipQty) AS MaxShipQty, 
	MAX(year.AvgShipQty) AS AvgShipQty, 
	MAX(year.StdShipQty) AS StdShipQty, 
	MAX(year.Customers) AS Customers,
	year.Plant
FROM         
	dbo.pa_1_0_q1_sales_history AS q1 RIGHT OUTER JOIN
	dbo.pa_1_1_q2_sales_history AS q2 RIGHT OUTER JOIN
	dbo.pa_1_2_q3_sales_history AS q3 RIGHT OUTER JOIN
	dbo.pa_1_3_q4_sales_history AS q4 RIGHT OUTER JOIN
	dbo.pa_1_4_year_sales_qty_data AS year ON q4.PartNum = year.PartNum AND 
	q4.Plant = year.Plant ON q3.PartNum = year.PartNum AND 
	q3.Plant = year.Plant ON q2.PartNum = year.PartNum AND 
	q2.Plant = year.Plant ON q1.PartNum = year.PartNum AND 
	q1.Plant = year.Plant
GROUP BY 
	year.PartNum, 
	year.Plant