none
Arithmetic overflow error converting money to data type numeric RRS feed

  • Question

  • Please what could possibly be causing this error in SSRS? 

    Arithmetic overflow error converting money to data type numeric

    below is my stored procedure.

    USE [ODS]
    GO
    /****** Object:  StoredProcedure [dbo].[proc_AvgLoans_MonthlyIncomeByMonth]    Script Date: 06/29/2013 17:45:16 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[proc_AvgLoans_MonthlyIncomeByMonth] --EXEC  dbo.proc_AvgLoans_MonthlyIncomeByMonth 

    @ReportDate Datetime,
    @LoanAmount varchar(max),
    @LoanPurpose varchar(max),
    @PropertyUsage varchar(max),
    @Demographics2 varchar(max)

    AS
    BEGIN


    DECLARE --@ReportDate datetime = '6/27/2013',
    @DayOfWeek varchar(100),
    @BeginOfWeek Datetime,
    @BeginOfMonth Datetime,
    @BeginOfYear Datetime

    SET @DayOfWeek = (SELECT DATENAME(dw,@ReportDate))
    SET @BeginOfWeek = (
    SELECT CASE WHEN @DayOfWeek = 'Sunday' THEN DATEADD(dd,0,@ReportDate)
    WHEN @DayOfWeek = 'Monday' THEN DATEADD(dd,-1,@ReportDate)
    WHEN @DayOfWeek = 'Tuesday' THEN DATEADD(dd,-2,@ReportDate)
    WHEN @DayOfWeek = 'Wednesday' THEN DATEADD(dd,-3,@ReportDate)
    WHEN @DayOfWeek = 'Thursday' THEN DATEADD(dd,-4,@ReportDate)
    WHEN @DayOfWeek = 'Friday' THEN DATEADD(dd,-5,@ReportDate)
    WHEN @DayOfWeek = 'Saturday' THEN DATEADD(dd,-6,@ReportDate) END BeginOfWeek
    )

    SET @BeginOfMonth = (
    SELECT CONVERT(DATETIME,CONVERT(VARCHAR(100),DATEPART(MM,@ReportDate)) + '/1/' + CONVERT(VARCHAR(100),DATEPART(YY,@ReportDate)))
    )

    SET @BeginOfYear = (
    SELECT '1/1/' + CONVERT(VARCHAR(100),DATEPART(YY,@ReportDate))
    )
    SELECT b.*,c.[Property Usage],d.MaritalStatus,d.Sex,d.Race,DATEDIFF(YY,d.DOB,@ReportDate) Age,MonthlyIncome,LoanAmount,
    CASE WHEN LoanDate >= @BeginOfWeek AND LoanDate <= @ReportDate THEN 'WeekToDate'
        WHEN LoanDate >= @BeginOfMonth AND LoanDate <= @ReportDate  THEN 'MonthToDate'
    WHEN LoanDate >= @BeginOfYear  AND LoanDate <= @ReportDate THEN 'YearToDate' ELSE NULL END ToDate,
    CASE WHEN LoanDate >= @BeginOfWeek AND LoanDate <= @ReportDate THEN 1
        WHEN LoanDate >= @BeginOfMonth AND LoanDate <= @ReportDate  THEN 2
    WHEN LoanDate >= @BeginOfYear  AND LoanDate <= @ReportDate THEN 3 ELSE NULL END ToDateOrder,
    CASE WHEN LoanAmount <= 100000 THEN 'Less Than $100k'
    WHEN LoanAmount between 100000 AND 200000 THEN '$100k to $200k'
    WHEN LoanAmount > 200000 THEN 'More Than $200k' END LoanAmountGroup,
    CASE WHEN LoanAmount <= 100000 THEN 1
    WHEN LoanAmount between 100000 AND 200000 THEN 2
    WHEN LoanAmount > 200000 THEN 3 END LoanAmountGroupOrder,
    CASE WHEN DATEDIFF(YY,d.DOB,@ReportDate) < 25 THEN '<=25'
    WHEN DATEDIFF(YY,d.DOB,@ReportDate) between 25 and 35 THEN '26-35'
    WHEN DATEDIFF(YY,d.DOB,@ReportDate) between 36 and 45 THEN '36-45'
    WHEN DATEDIFF(YY,d.DOB,@ReportDate) > 45 THEN '46+' END AgeGroup,
    CASE WHEN DATEDIFF(YY,d.DOB,@ReportDate) < 25 THEN 1
    WHEN DATEDIFF(YY,d.DOB,@ReportDate) between 25 and 35 THEN 2
    WHEN DATEDIFF(YY,d.DOB,@ReportDate) between 36 and 45 THEN 3
    WHEN DATEDIFF(YY,d.DOB,@ReportDate) > 45 THEN 4 END AgeGroupOrder
    INTO #Financials  
    FROM dbo.Fact_Financials a
    LEFT JOIN dbo.Dim_Loan b
    ON a.Loan_Key = b.Loan_Key
    LEFT JOIN Dim_Property c
    ON a.Property_Key = c.Property_Key
    LEFT JOIN dbo.Dim_Borrower d
    ON a.Borrower_Key = d.Borrower_Key

    SELECT *
    INTO #FinancialsII
    FROM #Financials
    WHERE LoanAmountGroup IN (SELECT items FROM dbo.Split(@LoanAmount,','))
    AND [Purpose of Loan] IN (SELECT items FROM dbo.Split(@LoanPurpose,','))
    AND [Property Usage] IN (SELECT items FROM dbo.Split(@PropertyUsage,','))
    AND (AgeGroup IN (SELECT items FROM dbo.Split(@Demographics2,','))
    OR MaritalStatus IN (SELECT items FROM dbo.Split(@Demographics2,','))
    OR Race IN (SELECT items FROM dbo.Split(@Demographics2,','))
    OR Sex IN (SELECT items FROM dbo.Split(@Demographics2,',')))


    SELECT DATEPART(MM,LoanDate) LoanMonth,DATEPART(YY,LoanDate) LoanYear,
    AVG(CONVERT(DECIMAL(10,2),MonthlyIncome)) AvgMonthlyIncome,AVG(CONVERT(DECIMAL(10,2),LoanAmount)) AvgLoanAmount,
    SUM(CONVERT(DECIMAL(10,2),MonthlyIncome) / CONVERT(DECIMAL(10,2),LoanAmount)) MonthlyIncomeVSLoanAmount
    FROM #FinancialsII
    WHERE LoanDate <= @ReportDate
    GROUP BY DATEPART(MM,LoanDate),DATEPART(YY,LoanDate)
    ORDER BY DATEPART(YY,LoanDate) DESC,DATEPART(MM,LoanDate) DESC



    END

    Wednesday, June 22, 2016 5:20 AM

Answers

  • You get this error when you are converting a money value to a decimal and the value in the column or variable you are converting cannot fit in the decimal size you specify.  In your case I suspect it is in the conversion of MonthlyIncome or LoanAmount to decimal(10,2).  The largest value that can fit in decimal(10,2) is 99,999,999.99.  One of you amounts must have a value larger than that.  You will have to convert to a larger decimal size.  You could use decimal(19,4) which will hold any possible money value.

    Tom

    • Marked as answer by Ninying Wednesday, June 22, 2016 6:46 AM
    Wednesday, June 22, 2016 6:00 AM

All replies

  • You get this error when you are converting a money value to a decimal and the value in the column or variable you are converting cannot fit in the decimal size you specify.  In your case I suspect it is in the conversion of MonthlyIncome or LoanAmount to decimal(10,2).  The largest value that can fit in decimal(10,2) is 99,999,999.99.  One of you amounts must have a value larger than that.  You will have to convert to a larger decimal size.  You could use decimal(19,4) which will hold any possible money value.

    Tom

    • Marked as answer by Ninying Wednesday, June 22, 2016 6:46 AM
    Wednesday, June 22, 2016 6:00 AM
  • This worked! Thank u Mr Tom!!

    QNN

    Wednesday, June 22, 2016 6:46 AM