Monthly Historic On Hand

1800radiatoranalytics of 1-800 Radiator-SQL Codes
4/13/2017 - 6:34 PM
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
Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

Monthly Historic On Hand
