bberkovich of 1800 Radiator
5/11/2016 - 6:59 PM

Weekly Historic On Hand

Weekly Historic On Hand



DECLARE @Whse INT
SET @whse = 472

DROP TABLE #Date
SELECT cal_week
	,CAST(MIN(Cal_Date) AS DATE) MinDate
INTO #Date
FROM misc_10.dbo.LU_DATE
GROUP BY cal_week
	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2008 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0  
	
	
UNION 	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2009 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0  
	
	
UNION 	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2010 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0  
	
	
UNION 	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2011 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0  
	
	
	
UNION 	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2012 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0  
	
	
UNION	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2013 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0   
	
	
	
UNION	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2014 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0  
	
	
UNION	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2015 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0  



UNION	
	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2016 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0  




UNION	


SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM ArchiveDB.dbo.StockWarehouseHistory_2017 SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0  
	
	
UNION 	
	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,RIGHT(DT.cal_week,2) [Week]
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END CategoryGroup
	,SUM(SW.StockWarehouseOnHand * SW.StockWarehouseCost ) OnHand 
	,SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost ) OnHandOnOrder 		
FROM RecentHistory_24.dbo.StockWarehouseHistory SW	
	JOIN product_10.dbo.lu_item IT
	  ON SW.StockItem = IT.item
	LEFT JOIN product_10.dbo.CategoryGroup CG
	  ON IT.category = CG.Category
	JOIN #Date DT
	  ON CAST(SW.EffectiveDate AS DATE) = DT.MinDate
WHERE Warehouseid = @whse	
	AND CG.GroupName <> 'Non-Inventory'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,RIGHT(DT.cal_week,2) 
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END
HAVING SUM( ( SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh ) * SW.StockWarehouseCost )  > 0