Solved

SQL Multiplication not working

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article describes how to programmatically preset the "Pages per Sheet" option that's available with most printer drivers.   This setting lets you do "n-Up" printing, where two, four, or more pages are printed on each sheet of paper. If your …
As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 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

8 Experts available now in Live!

Get 1:1 Help Now