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'