Solved

SQL Multiplication not working

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now