Results 1 to 4 of 4

052510, 18:36 #1Registered User
 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

052510, 19:13 #2Registered User
 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 SQL92 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.

052610, 04:28 #3King of Understatement
 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.

052610, 12:22 #4World Class Flame Warrior
 Join Date
 Jun 2003
 Location
 Ohio
 Posts
 12,592
Provided Answers: 1What 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
If it's not practically useful, then it's practically useless.
blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com