SQL Multiplication not working

Hi Guys

I have created a SQL trigger.

But one of the calculation does not give me the correct answer.

The calculation that it is failing on:

      ---------------------------------------------------------------------------------
      --Final Price ($/MT)
      --------------------------------------------------------------------------
      print'----------------------------------------------------------------'
      print ' Final Price ($/MT)'
      print'----------------------------------------------------------------'
      
      Set @cont_FinalPriceMT = @cont_FinalPricePU * @prod_newmtconvert
      
      print @cont_FinalPricePU
      print @prod_newmtconvert
      Print @cont_FinalPriceMT

      
      
      Update dbo.Contract
      set cont_FinalPriceMT  = @cont_FinalPriceMT
      where cont_ContractID = @cont_ContractID

The actual values are:

@cont_FinalPricePU  = 7516.77
@prod_newmtconvert = 2.00

The value it returns is 15033.53

The value should be 7516.77 * 2.00 = 15033.54

Thanks,
Shezad


Full trigger below:

USE [CRM]
GO
/****** Object:  Trigger [dbo].[PricingSummary_Totallotstoprice]    Script Date: 09/19/2014 09:44:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[PricingSummary_Totallotstoprice]
ON [dbo].[Contract]
AFTER INSERT, UPDATE, DELETE
AS 

BEGIN --' Total Lots to price'
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	Declare @cont_ContractID int,
     @cont_tonnage Money,
	 @prod_newunitlot Money,
	 @cont_goodstype int,
	 @cont_Totallotstoprice Money,
	@cont_Totallotstraded Money,
	@cont_Stilltotrade Money,
	@cont_originaltonnage Money,
	@cont_Balance Money,
	@cont_DiscountPU Money,
	@prod_newmtconvert Money,
	@cont_discountmt Money,
	@cont_price Money,
	@cont_PremiumMT Money,
	@cont_AveragePricedFuturesPU Money,
	@cont_AveragePricedFuturesMT Money,
	@cont_currentmarketPU Money,
	@cont_currentmarketMT Money,
	@SUM_trad_price Money,
	@cont_MarketValFeaturesPU Money,
	@cont_MarkedvalFuturesMT Money,
	@cont_PremiumPU Money,
	@cont_FinalPricePU Money,
	@cont_FinalPriceMT Money,
	@cont_AdjustPrice Money,
	@cont_FinalPrice Money,
    @cont_contracttype nvarchar(40),
    @cont_averageprice Money,
    @CONTRACT_averageprice Money,
    @SUM_trad_tonnes Money
 
    
	
	
	-------------------------------------------------------------------------------------------
	--Total Lots to price
	-------------------------------------------------------------------------------------------
	
	set @cont_ContractID = (Select cont_ContractID from Inserted)
	print ' Total Lots to price'
	print'------------------------------------------------------------------'
	print @cont_ContractID 
	
	set @cont_tonnage = (Select cont_tonnage from Inserted)
	--print @cont_tonnage 
	
    set @cont_goodstype = (Select cont_goodstype from Inserted)
    
	Set @prod_newunitlot = (Select prod_newunitlot from dbo.NewProduct where Prod_ProductID = @cont_goodstype)
	--print @prod_newunitlot
	
	if @prod_newunitlot = NULL
	Begin
	set @prod_newunitlot = 0
	End
	
	if @prod_newunitlot > 0
	begin
	set  @cont_Totallotstoprice = (select @cont_tonnage / @prod_newunitlot)
	
	print @cont_Totallotstoprice 
	
	update dbo.Contract
	set  cont_Totallotstoprice  = @cont_Totallotstoprice 
	where cont_ContractID = @cont_ContractID 
	
	end
	
	
	--------------------------------------------------------------------------------------------
	--Still to trade
	--------------------------------------------------------------------------------------------
	
	set @cont_ContractID = (Select cont_ContractID from Inserted)
	print ' Still to trade'
	print'------------------------------------------------------------------'
	print @cont_ContractID 
	
	set @cont_Totallotstoprice = (Select cont_Totallotstoprice from dbo.Contract where cont_ContractID = @cont_ContractID)
	set @cont_Totallotstraded = (Select cont_Totallotstraded from Inserted)
	
	set @cont_Stilltotrade = @cont_Totallotstoprice - @cont_Totallotstraded 
	
	update dbo.Contract
	set cont_Stilltotrade = @cont_Stilltotrade
	where cont_ContractID = @cont_ContractID
	
	---------------------------------------------------------------------------------------------
	--Balance
	---------------------------------------------------------------------------------------------
	set @cont_originaltonnage =(select cont_originaltonnage from Inserted)
	Set @cont_Balance =  @cont_tonnage -  @cont_originaltonnage 
	
	print ' Balance'
	print'------------------------------------------------------------------'
	
	print @cont_Balance
	
	update dbo.Contract
	set cont_Balance = @cont_Balance
	where cont_ContractID = @cont_ContractID
	
	----------------------------------------------------------------------------------------------------
	--Discount ($/MT)
	----------------------------------------------------------------------------------------------
	
	print ' Discount ($/MT)'
	print'------------------------------------------------------------------'
	
	set @cont_DiscountPU = (Select cont_DiscountPU from Inserted)
	
	print @cont_DiscountPU
	
	
	Set @prod_newmtconvert = (Select prod_newmtconvert from dbo.NewProduct where Prod_ProductID = @cont_goodstype)
	
	
	if @prod_newmtconvert = NULL
	Begin
	set @prod_newmtconvert = 0
	End
	
	print @prod_newmtconvert
	
	set @cont_discountmt = @cont_DiscountPU * @prod_newmtconvert 
	
	print @cont_discountmt
	
	
	update dbo.Contract
	set cont_discountmt = @cont_discountmt
	where cont_ContractID = @cont_ContractID
	
	
	------------------------------------------------------------------------------
	--Premium ($/MT)
	-------------------------------------------------------------------------------
		print ' Premium ($/MT)'
	print'------------------------------------------------------------------'
	
	Set @cont_PremiumPU  = (Select cont_PremiumPU  from Inserted)
	
	Print @cont_PremiumPU
	Print @prod_newmtconvert
	
	Set @cont_PremiumMT = @cont_PremiumPU * @prod_newmtconvert
	
	print @cont_PremiumMT
	
	update dbo.Contract
	set cont_PremiumMT = @cont_PremiumMT
	where cont_ContractID = @cont_ContractID
	
	
	-----------------------------------------------------------------------------
	--Average Priced Futures ($/MT)
	--------------------------------------------------------------------------
	
	print ' Average Priced Futures ($/MT)'
	print'------------------------------------------------------------------'
	
	Set @cont_AveragePricedFuturesPU = (Select cont_AveragePricedFuturesPU from dbo.Contract where cont_ContractID = @cont_ContractID)
	
	print @cont_AveragePricedFuturesPU
	
	set @cont_AveragePricedFuturesMT = @cont_AveragePricedFuturesPU * @prod_newmtconvert
	
	print @cont_AveragePricedFuturesMT
	
		update dbo.Contract
	set cont_AveragePricedFuturesMT = @cont_AveragePricedFuturesMT
	where cont_ContractID = @cont_ContractID
	
	
	-----------------------------------------------------------------------------
	--Current Market($/MT)
	--------------------------------------------------------------------------
	
	print ' Current Market($/MT)'
	print'------------------------------------------------------------------'
	
	Set @cont_currentmarketPU = (Select cont_currentmarketPU from Inserted)
	
	Print @cont_currentmarketPU 
	
	if @prod_newmtconvert > 0
	begin
	
	set @cont_currentmarketMT = @cont_currentmarketPU * @prod_newmtconvert
	
	Print @cont_currentmarketMT
	
	Update dbo.Contract
	set cont_currentmarketMT = @cont_currentmarketMT
	where cont_ContractID = @cont_ContractID
	
	end
	
	
	---------------------------------------------------------------------------------
	--Marked Valuation Futures(PU)
	--------------------------------------------------------------------------
	
	print ' Marked Valuation Futures(PU)'
	print'------------------------------------------------------------------'
	
	Print @cont_Stilltotrade
	
	Print @cont_currentmarketPU 
	
	Print @cont_Totallotstoprice 
	
  Set @CONTRACT_averageprice = (Select cont_averageprice from Inserted)
	
	print @CONTRACT_averageprice
	
	if @cont_Totallotstoprice > 0
	begin
	
	set @cont_MarketValFeaturesPU = @CONTRACT_averageprice + ((@cont_Stilltotrade * @cont_currentmarketPU) / @cont_Totallotstoprice )
	
	Print @cont_MarketValFeaturesPU
	
	Update dbo.Contract
	set cont_MarketValFeaturesPU  = @cont_MarketValFeaturesPU 
	where cont_ContractID = @cont_ContractID
	
	end
	
	
	---------------------------------------------------------------------------------
	--Marked Valuation Futures($/MT))
	--------------------------------------------------------------------------
	print'------------------------------------------------------------------'
	print ' Marked Valuation Futures($/MT)'
	print'------------------------------------------------------------------'
	
	print @cont_MarketValFeaturesPU 
	print @prod_newmtconvert

	Set @cont_MarkedvalFuturesMT = @cont_MarketValFeaturesPU * @prod_newmtconvert
	Declare  @test nvarchar(150)


	Set @test = @cont_MarketValFeaturesPU * @prod_newmtconvert

	print @test
	
	print @cont_MarkedvalFuturesMT

	Update dbo.Contract
	set cont_MarkedvalFuturesMT  = @cont_MarkedvalFuturesMT
	where cont_ContractID = @cont_ContractID
	
	---------------------------------------------------------------------------------
	--Final Price (PU)
	--------------------------------------------------------------------------
	print'------------------------------------------------------------------'
	print ' Final Price (PU)'
	print'------------------------------------------------------------------'
	
	Print @cont_MarketValFeaturesPU
	Print @cont_DiscountPU 
	
	Set @cont_PremiumPU = (Select cont_PremiumPU from Inserted)
	
	Print @cont_PremiumPU
	
	Set @cont_FinalPricePU = @cont_MarketValFeaturesPU - (@cont_DiscountPU + @cont_PremiumPU)
	
	Print @cont_FinalPricePU
	
	Update dbo.Contract
	set cont_FinalPricePU = @cont_FinalPricePU
	where cont_ContractID = @cont_ContractID
	
	---------------------------------------------------------------------------------
	--Final Price ($/MT)
	--------------------------------------------------------------------------
	print'----------------------------------------------------------------'
	print ' Final Price ($/MT)'
	print'----------------------------------------------------------------'
	
	Set @cont_FinalPriceMT = @cont_FinalPricePU * @prod_newmtconvert
	
	print @cont_FinalPricePU 
	print @prod_newmtconvert
	Print @cont_FinalPriceMT

	
	
	Update dbo.Contract
	set cont_FinalPriceMT  = @cont_FinalPriceMT
	where cont_ContractID = @cont_ContractID
	
	---------------------------------------------------------------------------------
	--Final Price
	--------------------------------------------------------------------------
	print'----------------------------------------------------------------'
	print ' Final Price'
	print'----------------------------------------------------------------'
	
	Set @cont_AdjustPrice = (Select cont_AdjustPrice from Inserted)
	
    Print @cont_AdjustPrice
    
   Set  @cont_FinalPrice = @cont_FinalPriceMT + @cont_AdjustPrice
   
   Print @cont_FinalPrice
   
    Update dbo.Contract
	set cont_FinalPrice  = @cont_FinalPrice
	where cont_ContractID = @cont_ContractID
	
	
	---------------------------------------------------------------------------------
	--cont_price
	--------------------------------------------------------------------------
	
	print ' cont_price'
	print'----------------------------------------------------------------'
	
	Set @cont_contracttype = (Select cont_contracttype from Inserted)
	
	Print @cont_contracttype
	
	if @cont_contracttype = 'Pricing'
	Begin
	
	Update dbo.Contract
	set  cont_price = @cont_FinalPrice
	where cont_ContractID = @cont_ContractID
	
	End
	
	
	------------------------------------------------------------------------------------
	--Average Priced Futures(PU)
	----------------------------------------------------------------------------------
	print'------------------------------------------------------------------'
	print ' Average Priced Futures(PU)'
	print'------------------------------------------------------------------'
  	
   Set @CONTRACT_averageprice = (Select cont_averageprice from Inserted)
	
	print @CONTRACT_averageprice
	

	Set @SUM_trad_tonnes = (select isnull(sum(trad_tonnes),0) as Value
	from dbo.Trades
	WHERE trad_Contract = @cont_ContractID AND trad_Deleted IS NULL)
	
	print @SUM_trad_tonnes
	
	if @SUM_trad_tonnes > 0
	Begin
	
	Set @cont_AveragePricedFuturesPU = @CONTRACT_averageprice / @SUM_trad_tonnes
	
	print @cont_AveragePricedFuturesPU
	
	UPDATE dbo.Contract
	SET cont_AveragePricedFuturesPU = @cont_AveragePricedFuturesPU
	WHERE cont_ContractID =  @cont_ContractID
	
	end
	
	
	
	
	
	END --Last END

Select allOpen in new window