Solved

SQL Multiplication not working

Posted on 2014-09-19
3
100 Views
Last Modified: 2014-09-20
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

Open in new window

0
Comment
Question by:Shezad Ahmed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40332489
7516.765 * 2 = 15033.53

but 7516.765 would be seen as 7516.77 when looked at by a default of 2 decimal places.

the money data type uses 4 decimals places
0
 

Author Closing Comment

by:Shezad Ahmed
ID: 40334269
Hi

Got it working by declaring as NUMERIC(24,2).

Cheers
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to make a Windows 7 gadget that accepts files dropped from the Windows Explorer.  It also illustrates how to give your gadget a non-rectangular shape and how to add some nifty visual effects to text displayed in a your gadget.…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question