std-avg-test.sql

SELECT 
	PartNum, 
	CONVERT(DECIMAL(16, 4), (SUM(OurShipQty) * .5)) AS QuarterTotal, 
	CONVERT(DECIMAL(16, 4), (SUM(OurShipQty) / 3) * .5) AS MonthAvg,
	CONVERT(DECIMAL(16, 4), (COUNT(PartNum)) * .5) AS Hits,
	COUNT(DISTINCT CustID) AS Customers,
	MAX(Plant) AS Plant
FROM 
	dbo.v_InvoiceRegisterForTurns
WHERE
	(InvoiceDate > DATEADD(month, - 12, GETDATE())) AND 
	(InvoiceDate <= DATEADD(month, - 9, GETDATE())) AND 
	(InvoiceTerms NOT IN ('16', '26', '14', 'PORD', '50%D', '50%S')) AND 
	(NetExtPrice > 0) AND
	(BuyToOrder <> 'TRUE')
GROUP BY 
	PartNum,
	Plant

SELECT
Register.PartNum,
CONVERT(DECIMAL(16, 4), (SUM(OurShipQty) * .5)) AS QuarterTotal,
CONVERT(DECIMAL(16, 4), (SUM(OurShipQty) / 3) * .5) AS MonthAvg,
CONVERT(DECIMAL(16, 4), (COUNT(Register.PartNum)) * .5) AS Hits,
COUNT(DISTINCT CustID) AS Customers, MAX(Plant) AS Plant, AVG(AvgQty) as AvgQty, AVG(StdQty) as StdQty
FROM
EpicorReports.dbo.v_InvoiceRegisterForTurns AS Register Left Join
(SELECT
AVG(OurShipQty) * .5 AS AvgQty, PartNum
FROM
EpicorReports.dbo.v_InvoiceRegisterForTurns AS IR
WHERE
--IR.PartNum = Register.PartNum AND
(InvoiceDate > DATEADD(month, - 12, GETDATE())) AND
(InvoiceDate <= DATEADD(month, - 9, GETDATE())) AND
(InvoiceTerms NOT IN ('16', '26', '14', 'PORD', '50%D', '50%S')) AND
(NetExtPrice > 0) AND
(BuyToOrder <> 'TRUE')
GROUP BY PartNum
) AS AvgQty on Register.PartNum = AvgQty.PartNum Left Join

(SELECT 
	STDEV(OurShipQty) * .5 AS StdQty, PartNum 
	FROM  
		EpicorReports.dbo.v_InvoiceRegisterForTurns AS IR 
	WHERE 
		--IR.PartNum = Register.PartNum AND 
		(InvoiceDate > DATEADD(month, - 12, GETDATE())) AND  
		(InvoiceDate <= DATEADD(month, - 9, GETDATE())) AND  
		(InvoiceTerms NOT IN ('16', '26', '14', 'PORD', '50%D', '50%S')) AND  
		(NetExtPrice > 0) AND 
		(BuyToOrder <> 'TRUE') 
	GROUP BY PartNum
) AS StdQty on Register.PartNum = StdQty.PartNum

WHERE
(InvoiceDate > DATEADD(month, - 12, GETDATE())) AND
(InvoiceDate <= DATEADD(month, - 9, GETDATE())) AND
(InvoiceTerms NOT IN ('16', '26', '14', 'PORD', '50%D', '50%S')) AND
(NetExtPrice > 0) AND
(OurShipQty) < (AvgQty + StdQty) AND
(OurShipQty) > (AvgQty - StdQty) AND
(BuyToOrder <> 'TRUE')
GROUP BY
Register.PartNum,
Plant