thing.sql

SELECT DISTINCT 
	Epicor10.Erp.Part.PartNum, 
	dbo.pa_2_0_year_sales_history.Hits, 
	Epicor10.Erp.Part.PUM, 
	dbo.pa_2_0_year_sales_history.Customers, 
	Epicor10.Erp.Part.IUM, 
	Epicor10.Erp.PartPlant.MinimumQty, 
	Epicor10.Erp.PartPlant.MaximumQty, 
	Epicor10.Erp.PartPlant.LeadTime, 
	Epicor10.Erp.PartPlant.VendorNum, 
	Epicor10.Erp.PartPlant.Plant, 
	Epicor10.Erp.PartPlant.SourceType,
	
	CASE WHEN Epicor10.ERP.Part.PUM = 'DZ' 
		THEN '12' 
	WHEN Epicor10.Erp.PartUOM.ConvFactor IS NULL 
		THEN '1' 
	ELSE 
		Epicor10.Erp.PartUOM.ConvFactor 
	END AS ConvFactor,
	
	CASE WHEN (SugMin < ConvFactor) 
		THEN CONVERT(DECIMAL(16, 4), CEILING(ConvFactor - SugMin)) 
	WHEN (SugMin % ConvFactor) <> 0 
		THEN CONVERT(DECIMAL(16, 4), (CEILING(SugMin / ConvFactor) * ConvFactor - SugMin)) 
	ELSE 
		0 
	END AS MinMod, 
	
	CASE WHEN (SugMax < ConvFactor) 
		THEN CONVERT(DECIMAL(16, 4), CEILING(ConvFactor - SugMax)) 
	WHEN (SugMax % ConvFactor) <> 0 
		THEN CONVERT(DECIMAL(16, 4), (CEILING(SugMax / ConvFactor) * ConvFactor - SugMax)) 
	ELSE 
		0 
	END AS MaxMod
FROM         
	dbo.pa_2_0_year_sales_history RIGHT OUTER JOIN
	Epicor10.Erp.PartUOM RIGHT OUTER JOIN
	Epicor10.Erp.PartPlant LEFT OUTER JOIN
	Epicor10.Erp.Part ON Epicor10.Erp.PartPlant.PartNum = Epicor10.Erp.Part.PartNum ON Epicor10.Erp.PartUOM.Company = Epicor10.Erp.Part.Company AND 
	Epicor10.Erp.PartUOM.PartNum = Epicor10.Erp.Part.PartNum AND 
	Epicor10.Erp.PartUOM.UOMCode = Epicor10.Erp.Part.PUM ON dbo.pa_2_0_year_sales_history.Plant = Epicor10.Erp.PartPlant.Plant AND 
	dbo.pa_2_0_year_sales_history.PartNum = Epicor10.Erp.PartPlant.PartNum
WHERE     
	(Epicor10.Erp.Part.TypeCode <> 'K') AND 
	(Epicor10.Erp.PartPlant.BuyToOrder <> 'true')