Skip to content
Advertisement

Arithmetic overflow error converting nvarchar to data type numeric in case query

I am trying to show the number of quotes won as a percentage of the total number of quotes in a time frame. For example. I have a total of 294 quotes and 173 of them are won. The result I am looking for is 59%.

I have the below query but it is returning the error:

Arithmetic overflow error converting nvarchar to data type numeric

SELECT 
    COUNT(dbo.Quotes.qmpQuoteID *
    CASE WHEN dbo.QuoteLines.qmlResolutionReasonID = 'WON' THEN 100
    ELSE 0.0 END) As WonRate
FROM dbo.Quotes
LEFT OUTER JOIN dbo.QuoteLines on dbo.Quotes.qmpQuoteID = dbo.QuoteLines.qmlQuoteID 
LEFT OUTER JOIN dbo.Reasons ON dbo.QuoteLines.qmlResolutionReasonID = dbo.Reasons.xarReasonID
WHERE uqmpDateSent >= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()) -3,0)
AND uqmpDateSent < DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
AND dbo.Quotes.UQMPCANCELLED = 0
AND dbo.QuoteLines.qmlResolutionReasonID != 'PROFO'
AND dbo.QuoteLines.qmlResolutionReasonID != 'SMSRQ'
AND dbo.QuoteLines.qmlResolutionReasonID != 'OPTIO' 
AND dbo.QuoteLines.qmlResolutionReasonID != 'PRICE'
AND dbo.QuoteLines.qmlResolutionReasonID <> ''

Advertisement

Answer

Try

SELECT 

    WonRate = SUM(Q.qmpQuoteID * (CASE WHEN L.qmlResolutionReasonID = <See Note 1> THEN 100 ELSE 0 END))

FROM            dbo.Quotes      Q
    LEFT JOIN   dbo.QuoteLines  L   ON  L.qmpQuoteID    =   Q.qmlQuoteID 
    LEFT JOIN   dbo.Reasons     R   ON  R.xarReasonID   =   L.qmlResolutionReasonID
    
WHERE   uqmpDateSent    >=  DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()) -3,0)
    AND uqmpDateSent    <   DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
    AND Q.UQMPCANCELLED = 0
    AND L.qmlResolutionReasonID NOT IN ('PROFO', 'SMSRQ', 'OPTIO', 'PRICE', '')

--Note 1: I think you need to put here the ReasonID value for the Reason Description = 'WON'
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement