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

Monthly Historic On Hand

Monthly Historic On Hand

DECLARE @Month CHAR(2)

SET @Month = 01
DECLARE @Whse INT
SET @whse = 472
	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2008-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END 
	
	
UNION 	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2009-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END 
	
	
UNION 	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2010-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END 
	
	
UNION 	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2011-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END 
	
	
	
UNION 	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2012-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END 
	
	
UNION	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2013-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END 
	
	
	
UNION	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2014-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END 
	
	
UNION	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2015-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END  



UNION	
	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2016-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END 




UNION	
	
	
SELECT SW.Warehouseid	
	,YEAR(SW.EffectiveDate) [Year]
	,MONTH(SW.EffectiveDate) [Month]
	,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
WHERE Warehouseid = @whse	
	AND EffectiveDate = '2016-'+@Month+'-01'
GROUP BY SW.Warehouseid	
	,YEAR(SW.EffectiveDate)
	,MONTH(SW.EffectiveDate)
	,CASE WHEN CG.GroupName IS NULL THEN 'Other' ELSE CG.GroupName END