dayskipper.sql

SELECT (SUM(ExtendedCost) / 
  (SELECT COUNT(*) AS TempCount FROM
	(SELECT SnapDate
		FROM EpicorReports.dbo.InventorySnapshots AS DayCount
		WHERE Datepart(week,DayCount.SnapDate) = Datepart(week,InvSnap.SnapDate) AND Plant <> 'PROJ'
		GROUP BY SnapDate) 
	AS NumOfDays)) 
AS OnHandValue,
ItemMoveType_c, DATEPART(week, SnapDate) AS Week
FROM        EpicorReports.dbo.InventorySnapshots AS InvSnap
WHERE     (Plant <> 'PROJ') AND (DATEPART(year, SnapDate) > 2017) AND (DATEPART(week, SnapDate) < DATEPART(week, GETDATE())) AND (BuyToOrder = 'FALSE')
GROUP BY DATEPART(week, SnapDate), ItemMoveType_c
ORDER BY Week