Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Unanswered: Adding decimal types together and they keep rounding to whole numbers??

    Hi,

    I have a stored procedure that adds up some values of DECIMAL data type and then outputs them in a SELECT statement. For whatever reason, it has been rounding to whole numbers. It's either 1.00 or 0.00 and nothing in between. Here is my code. The variable in question is @TotalPercentage. Any suggestions?

    Code:
    CREATE TABLE #Payments
    		(
    		RowId INT IDENTITY(1,1),
    		InvoiceYear INT,
    		InvoiceId VARCHAR(4),
    		PaymentType VARCHAR(10),
    		Percentage DECIMAL(10,9),
    		Amount MONEY,
    		TurnedDown BIT,
    		BankServiceCharge MONEY,
    		BSCTurnedDown BIT,
    		InvoicedAmount MONEY
    		)
    	
    		DECLARE @PaymentsNumberRecords INT, @PaymentsRowCount INT
    		
    		DECLARE @InvoiceYear INT, 
    			@InvoiceId VARCHAR(4), 
    			@PaymentType VARCHAR(10), 
    			@Percentage DECIMAL(10,9), 
    			@Amount MONEY, @TurnedDown BIT, 
    			@BankServiceCharge MONEY, 
    			@BSCTurnedDown BIT, 
    			@InvoicedAmount MONEY
    		
    		DECLARE @TotalPercentage DECIMAL(10,9), 
    			@TotalDraftAmount MONEY, 
    			@TotalBankServiceCharge MONEY, 
    			@TotalCheckAmount MONEY, 
    			@TotalInvoicedAmount MONEY, 
    			@TotalDifference MONEY,
    			@InvoiceNumbersList VARCHAR(3000)
    	
    		INSERT INTO #Payments (InvoiceYear, InvoiceId, PaymentType, Percentage, Amount, TurnedDown, BankServiceCharge, BSCTurnedDown, InvoicedAmount)
    			SELECT pa.InvoiceYear, pa.InvoiceId, pa.PaymentType, pa.Percentage, pa.Amount, pa.TurnedDown, pa.BankServiceCharge, pa.BSCTurnedDown, pa.InvoicedAmount FROM ACCOUNTING.PAYMENT pa
    			INNER JOIN LANDMAN.LEASE le ON pa.LeaseId = le.LeaseId
    			WHERE le.LeaseId = @LeaseId
    	
    		SET @PaymentsNumberRecords = @@ROWCOUNT
    		SET @PaymentsRowCount = 1
    
    		SET @TotalPercentage = 0
    		SET @TotalDraftAmount = 0
    		SET @TotalBankServiceCharge = 0
    		SET @TotalCheckAmount = 0
    		SET @TotalInvoicedAmount = 0
    		
    		SET @InvoiceNumbersList = ''
    	
    		DECLARE @_draftPaymentType VARCHAR(10) = 'draft'
    		DECLARE @_checkPaymentType VARCHAR(10) = 'check'
    	
    		WHILE @PaymentsRowCount <= @PaymentsNumberRecords
    		BEGIN
    			SELECT @InvoiceYear = InvoiceYear, @InvoiceId = InvoiceId, @PaymentType = PaymentType, @Percentage = Percentage, @Amount = Amount, @TurnedDown = TurnedDown, @BankServiceCharge = BankServiceCharge, @BSCTurnedDown = BSCTurnedDown, @InvoicedAmount = InvoicedAmount 
    				FROM #Payments WHERE RowId = @PaymentsRowCount
    		
    			--Why the hell is it rounding this up to whole numbers? WHY?????????
    			SET @TotalPercentage = @TotalPercentage + @Percentage
    		
    			--This consarned mound of pooh doesn't work with @TurnedDown <> 1 as the condition?  WHY???????
    			IF (@TurnedDown IS NULL OR @TurnedDown = 0)
    			BEGIN
    				IF (@PaymentType = @_draftPaymentType)
    				BEGIN
    					SET @TotalDraftAmount = @TotalDraftAmount + @Amount
    					
    					IF (@BSCTurnedDown IS NULL OR @BSCTurnedDown = 0)
    					BEGIN
    						SET @TotalBankServiceCharge = @TotalBankServiceCharge + @BankServiceCharge
    					END
    				END
    				ELSE IF (@PaymentType = @_checkPaymentType)
    				BEGIN
    					SET @TotalCheckAmount = @TotalCheckAmount + @Amount
    				END
    			END
    		
    			SET @TotalInvoicedAmount = @TotalInvoicedAmount + @InvoicedAmount
    		
    			SET @TotalDifference = (@TotalDraftAmount + @TotalBankServiceCharge + @TotalCheckAmount) - @TotalInvoicedAmount
    			
    			SET @InvoiceNumbersList = CAST(@InvoiceYear AS VARCHAR(4)) + '-' + @InvoiceId
    			
    			IF (@PaymentsRowCount <= @PaymentsNumberRecords - 1)
    			BEGIN
    				SET @InvoiceNumbersList = @InvoiceNumbersList + ', '
    			END
    			
    			SET @PaymentsRowCount = @PaymentsRowCount + 1
    		END
    	
    		--This way, if there is nothing then it shows nothing and not the stupid
    		--number 0.00, which is so phoney balogna it makes me want to scream my head off.
    		IF (@TotalPercentage = 0)
    		BEGIN
    			SET @TotalPercentage = NULL
    		END
    		
    		IF (@TotalDraftAmount = 0)
    		BEGIN
    			SET @TotalDraftAmount = NULL
    		END
    	
    		IF (@TotalBankServiceCharge = 0)
    		BEGIN
    			SET @TotalBankServiceCharge = NULL
    		END
    		
    		IF (@TotalCheckAmount = 0)
    		BEGIN
    			SET @TotalCheckAmount = NULL
    		END
    		
    		IF (@TotalInvoicedAmount = 0)
    		BEGIN
    			SET @TotalInvoicedAmount = NULL
    		END
    	
    		DROP TABLE #Payments
    				
    		--Create temp table to contain locations linked to lease/payment.
    		CREATE TABLE #Locations
    		(
    		RowId INT IDENTITY(1,1),
    		Township VARCHAR(4),
    		Range VARCHAR(4),
    		Section INT,
    		LegalDescription VARCHAR(2000)
    		)
    	
    		DECLARE @LocationsNumberRecords INT, @LocationsRowCount INT
    		
    		DECLARE @Township VARCHAR(4), 
    			@Range VARCHAR(4), 
    			@Section INT, 
    			@LegalDescription VARCHAR(3000),
    			@LocationsList VARCHAR(3000)
    	
    		INSERT INTO #Locations (Township, Range, Section, LegalDescription)
    			SELECT Township, Range, Section, LegalDescription FROM LANDMAN.LOCATION lo 
    			INNER JOIN LANDMAN.LOCATION_LEASE lo_le ON lo.LocationId = lo_le.LocationId
    			INNER JOIN LANDMAN.LEASE le ON lo_le.LeaseId = le.LeaseId 
    			WHERE le.LeaseId = @LeaseId
    			ORDER BY Township ASC, Range ASC, Section ASC, LegalDescription ASC
    	
    		SET @LocationsNumberRecords = @@ROWCOUNT
    		SET @LocationsRowCount = 1
    		
    		SET @LocationsList = ''
    	
    		WHILE @LocationsRowCount <= @LocationsNumberRecords
    		BEGIN
    			SELECT @Township = Township, @Range = Range, @Section = Section, @LegalDescription = LegalDescription 
    				FROM #Locations WHERE RowId = @LocationsRowCount
    		
    			SET @LocationsList = @LocationsList + @Township + '-' + @Range + ', ' + CAST(@Section AS VARCHAR(2)) + ': ' + @LegalDescription
    		
    			IF (@LocationsRowCount <= @LocationsNumberRecords - 1)
    			BEGIN
    				SET @LocationsList = @LocationsList + '; '
    			END
    		
    			SET @LocationsRowCount = @LocationsRowCount + 1
    		END
    	
    		DROP TABLE #Locations
    	
    		INSERT INTO #DraftsDueList (LeaseId, EarliestPaymentDate, TotalPercentage, TotalDraftAmount, TotalBankServiceCharge, TotalCheckAmount, TotalInvoicedAmount, TotalDifference, InvoiceNumbersList, LocationsList) 
    			VALUES (@LeaseId, @EarliestPaymentDate, @TotalPercentage, @TotalDraftAmount, @TotalBankServiceCharge, @TotalCheckAmount, @TotalInvoicedAmount, @TotalDifference, @InvoiceNumbersList, @LocationsList)
    	
    		SET @LeasesRowCount = @LeasesRowCount + 1	
    	END
        
    	SELECT DISTINCT le.LeaseId, le.CreatedBy, le.CreatedDate, le.EditedBy, le.EditedDate, pr.Name,
    		le.LeaseReceivedDate, ls.LeaseStyling, ddl.LocationsList, 
    		le.SurfaceInspectionDate, le.TitleDueDate, le.TitleOrderedDate, le.TitleReceived,  
    		ddl.EarliestPaymentDate, ddl.TotalPercentage, ddl.TotalDraftAmount, ddl.TotalBankServiceCharge, 
    		ddl.TotalCheckAmount, ddl.InvoiceNumbersList, ddl.TotalInvoicedAmount, ddl.TotalDifference,
    		le.TransmittalDate, le.LeaseBuyer, le.Comments 
    		FROM SYSTEM.ACCOUNT ac
    		INNER JOIN SYSTEM.ACCOUNT_PROSPECT ac_pr ON ac.UserName = ac_pr.UserName
    		INNER JOIN ACCOUNTING.PROSPECT pr ON ac_pr.ProspectCode = pr.ProspectCode
    		INNER JOIN LANDMAN.LEASE le ON pr.ProspectCode = le.ProspectCode 
    		INNER JOIN ACCOUNTING.PAYMENT pa ON le.LeaseId = pa.LeaseId
    		INNER JOIN #DraftsDueList ddl ON le.LeaseId = ddl.LeaseId
    		INNER JOIN LANDMAN.LESSOR ls ON le.LessorId = ls.LessorId 
    		WHERE ac.UserName = @UserName
    		ORDER BY ddl.EarliestPaymentDate ASC, pr.Name ASC, ls.LeaseStyling ASC
    Paul Palubinski

  2. #2
    Join Date
    Jan 2010
    Posts
    18
    Decimal (10,9) gives 1 digit to the left of the decimal point and up to 9 digits to the right of the decimal point.

    Check out the following from decimal and numeric.

    decimal[(p[, s])] and numeric[(p[, s])]

    Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

    p (precision)

    Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

    s (scale)

    Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Paul

    Assuming you still are none the wiser, can you cut out any extraneous code and create a proof of concept? There is lots of code there that is irrelevant to the problem. I've glanced at it but would need to study it for a while just to identify the relevant sections.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is the datatype of ACCOUNTING.PAYMENT.Percentage?

    Add a line to your code and tell us what the output is:

    Code:
    			--Why the hell is it rounding this up to whole numbers? WHY?????????
    			print @TotalPercentage
    			print @Percentage
    			SET @TotalPercentage = @TotalPercentage + @Percentage
    			print @TotalPercentage
    What is the datatype of #DraftsDueList.TotalPercentage?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •