Cacher is the code snippet organizer for pro developers

									
IF OBJECT_ID('tempdb..#RegRank') IS NOT NULL											
	drop table #RegRank										
											
SELECT whdc											
	,category										
	,finalcuc										
	,SUM(whquant1yr) RegionalSales										
	,SUM(whlookups1yr) RegionalLookups										
	,ROW_NUMBER() OVER(PARTITION BY whdc,category ORDER BY SUM(whquant1yr) DESC, ntlrk1yr) RegSalesRank										
	,ROW_NUMBER() OVER(PARTITION BY whdc,category ORDER BY SUM(WHlookups1yr) DESC, ntlrk1yr) RegLookupRank																			
INTO #RegRank											
FROM product_24.dbo.inv_CUC_WH											
WHERE category IN ('radiator','ac-cond','comprex','fan_ass','Snd_mod')--,'drierx','achosex')											
	and finalcuc NOT LIKE '%00000%'										
GROUP BY whdc											
	,category										
	,finalcuc										
	,ntlrk1yr										

											



/**********************************************************											
											
	 Create table with CUC level details 										
	    - to calculate weighting per CUC 										
											
**********************************************************/											

DROP TABLE #RegDetails
SELECT a.*
	,b.CategoryTotalSales TotalSales
	,a.RegionalSales*1.0 / b.CategoryTotalSales*1.0 SalesWeighting
INTO #RegDetails	
FROM #RegRank a
	LEFT JOIN (
			SELECT whdc
				,category
				,SUM(RegionalSales) CategoryTotalSales
			FROM #RegRank
			GROUP BY whdc
				,category
				) b
		ON a.whdc = b.whdc
		AND a.category = b.category			
								
									
											
											
/*********************************************************************************											
											
	 Create base table that has every warehouse/CUC combination for OOS										
											
*********************************************************************************/											
IF OBJECT_ID('tempdb..#InStock') IS NOT NULL											
	drop table #InStock										
											
SELECT W.whseregion											
	,W.warehouseid										
	,RD.Category										
	,RD.finalcuc		
	,CAST(0 AS INT) LateModelInd								
	,RD.RegSalesRank										
	,RD.TotalSales RegionalCategorySales
	,CAST(0 AS INT) [1yr Lookups]
	,CAST(0 AS INT) [1yr Sales]
	,CAST(0 AS INT) DaysOOS	
	,rd.SalesWeighting								
	--,CAST(0 AS BIT) InStock										
	,CAST(0 AS BIT) OnHand
	,CAST(0 AS BIT) OnOrder										
	,CAST(0 AS INT) CurrrentROH
	,CAST(0 AS INT) RecROH										
	,CAST(0 AS INT) Priority										
INTO #InStock											
FROM #RegDetails RD											
	CROSS JOIN _Boris.dbo.VW_Warehouse W										
where RD.whdc = W.whseregion
									
											
											
-- Update on Hand by warehouse											
UPDATE I											
SET I.OnHand = OH.OnHand
	,I.OnOrder = OH.OnOrder
FROM #InStock I											
	 JOIN (										
		SELECT SW.Warehouseid									
			,IT.cuc								
			,SUM(SW.StockWarehouseOnHand) OnHand
			,SUM(SW.StockWarehouseOnOrderHigh + SW.StockWarehouseOnOrderHigh) OnOrder
		FROM gxWizmo_24.dbo.STOCKWAREHOUSE SW									
			JOIN product_10.dbo.lu_item IT								
			  ON SW.StockItem = IT.item	
		WHERE SW.StockWarehouseOnHand + SW.StockWarehouseOnOrderHigh + SW.StockWarehouseOnOrderHigh > 0 
		GROUP BY SW.Warehouseid									
			,IT.cuc								
		) OH									
	ON I.WarehouseID = OH.Warehouseid										
	 AND I.finalcuc = OH.cuc										
											


											
											
-- Update warehouse Sales Qty & RecROH											
UPDATE I											
SET I.[1yr Sales] = RO.WHquant1yr											
	,I.[1yr Lookups] = RO.WHLookups1yr
	,I.RecROH = ISNULL(RO.newbaseno,0)
	,I.LatemodelInd = RO.latemodelind
	,I.DaysOOS = RO.DaysOOS										
FROM #InStock I											
	JOIN (SELECT rte_wh										
				,finalcuc							
				,whquant1yr							
				,WHLookups1yr
				,latemodelind
				,newbaseno	
				,DaysOOS						
		  FROM product_24.dbo.inv_cuc_wh									
		  ) RO									
	   ON I.WarehouseID = RO.rte_wh										
	    AND I.finalcuc = RO.finalcuc

	    


	
-- Update Current ROH											
UPDATE I											
SET I.CurrrentROH = ISNULL(SU.SUGROHROH,0)
FROM #InStock I											
	JOIN gxWizmo_24.dbo.SUGROH SU
	  ON I.WarehouseID = SU.Warehouseid
	   AND I.finalcuc = SU.StockCUC
	    

	  										
-- Delete records WHERE Recommended RecROH = 0											
DELETE FROM #InStock											
WHERE RecROH = 0
	and Category IN (SELECT DISTINCT Category FROM #InStock WHERE RecROH > 0 )  											



											
											
----  Set Priorities for each CUC based on Regional Rank WHERE RecROH > 0											
-- Priority number is used to select the top 450 ranked Radiators - and excluding parts where RecROH = 0 											
UPDATE I											
SET I.Priority = C.Priority											
FROM #InStock I											
	JOIN  (										
			SELECT Warehouseid								
				,finalcuc							
				,ROW_NUMBER() OVER (Partition BY WarehouseID, Category ORDER BY RegSalesRank) Priority 							
			FROM #InStock								
			) C								
	 ON I.WarehouseID = C.WarehouseID										
	  AND I.Finalcuc = C.Finalcuc										
											
											

/*
Only need data for top parts: 
Radiators = top 450 regional
Condensers = top 250 regional
Fans= top 200 Regional
Snd_mod = top 200 regional
*/											
											
-- Core Category Maintenance											
-- Delete records that shouldn't be counted in the OOS Calculation											
DELETE FROM I											
-- SELECT I.* 											
FROM #InStock I																				
WHERE  ( I.category = 'Radiator'  AND Priority > 450) 										
	OR ( I.category = 'ac-cond'   AND Priority > 250)								
	OR ( I.category = 'Comprex'   AND Priority > 150)							
	OR ( I.category = 'Fan_Ass'   AND Priority > 200)							
	OR ( I.category = 'snd_mod'   AND Priority > 200)								
											
											
							
-- Clean up all other categories											
-- Delete records that shouldn't be counted in the OOS Calculation											
DELETE FROM I											
--SELECT I.* 											
FROM #InStock I																				
WHERE I.category NOT IN ( 'Radiator' ,'ac-cond','Comprex','Fan_Ass','snd_mod')																				

	
DROP TABLE #Temp1
SELECT a.*
	,b.Weighting
	,CASE WHEN OnHand > 0 THEN SalesWeighting ELSE 0 END UseThis
		,CASE WHEN a.category = 'Radiator' AND priority BETWEEN 1 AND 25 THEN '1 To 25'
		  WHEN a.category = 'Radiator' AND priority BETWEEN 26 AND 100 THEN '26 To 100'
		  WHEN a.category = 'Radiator' AND priority BETWEEN 101 AND 200 THEN '101 To 200'
		  WHEN a.category = 'Radiator' AND priority BETWEEN 201 AND 450 THEN '201 To 450'
		  WHEN a.category = 'Ac-Cond' AND priority BETWEEN 1 AND 25 THEN '1 To 25'
		  WHEN a.category = 'Ac-Cond' AND priority BETWEEN 26 AND 100 THEN '26 To 100'
		  WHEN a.category = 'Ac-Cond' AND priority BETWEEN 101 AND 250 THEN '101 To 250'
		  WHEN a.category = 'Fan_Ass' AND priority BETWEEN 1 AND 25 THEN '1 To 25'
		  WHEN a.category = 'Fan_Ass' AND priority BETWEEN 26 AND 100 THEN '26 To 100'
		  WHEN a.category = 'Fan_Ass' AND priority BETWEEN 101 AND 250 THEN '101 To 200'
		  WHEN a.category = 'Snd_Mod' AND priority BETWEEN 1 AND 10 THEN '1 To 10'
		  WHEN a.category = 'Snd_Mod' AND priority BETWEEN 11 AND 25 THEN '11 To 25'
		  WHEN a.category = 'Snd_Mod' AND priority BETWEEN 26 AND 50 THEN '26 To 50'
		  WHEN a.category = 'Snd_Mod' AND priority BETWEEN 51 AND 200 THEN '51 And 200'
		  WHEN a.category = 'Comprex' AND priority BETWEEN 1 AND 25 THEN '1 To 25'
		  WHEN a.category = 'Comprex' AND priority BETWEEN 26 AND 75 THEN '26 To 75'
		  WHEN a.category = 'Comprex' AND priority BETWEEN 76 AND 150 THEN '76 AND 150'
			END Bucket
INTO #Temp1	
FROM #InStock a
	JOIN (
		SELECT warehouseid
			,category
			,SUM(salesweighting) Weighting
		FROM #InStock
		GROUP BY warehouseid
			,category
	) b
		ON a.warehouseid = b.warehouseid
		AND a.category = b.category	
		


SELECT warehouseid
	,category
	,Bucket
	,SUM(CASE WHEN OnHand = 0 THEN 1 ELSE 0 END)*1.0 OOS
FROM #Temp1
GROUP BY warehouseid
	,category
	,Bucket

UNION

SELECT warehouseid	
	,category
	,'Total OOS' Bucket
	,1 - (SUM(UseThis) / Weighting) OOS
FROM #Temp1
GROUP BY warehouseid
	,category
	,Weighting
	
ORDER BY warehouseid
	,category
	,Bucket