Solved

SQL Multiplication not working

Posted on 2014-09-19
3
97 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 48

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

If you have ever found yourself doing a repetitive action with the mouse and keyboard, and if you have even a little programming experience, there is a good chance that you can use a text editor to whip together a sort of macro to automate the proce…
In this article, I will show how to use the Ribbon IDs Tool Window to assign the built-in Office icons to a ribbon button.  This tool will help us to find the OfficeImageId that corresponds to our desired built-in Office icon. The tool is part of…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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