1800radiatoranalytics of 1-800 Radiator-SQL Codes
4/13/2017 - 6:34 PM

2010 -- 2017 and Recent 90 Day Weekly On Hand *** This will need updating when ever a new year comes in.

2010 -- 2017 and Recent 90 Day Weekly On Hand

*** This will need updating when ever a new year comes in.

HistoricWeeklyInventory.sql
content_copyfile_download


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