asked on

SQL statement to show proposal win loss ratio by month over a given period of time

Hi,

I need help to construct a SQl query that shows proposal win loss ratio by month over a given period of time

Table name: Proposals
Column:
CreateDate (datetime)
ContAmt(float)
IsWin (bit)

Query1
SELECT substring(CONVERT(varchar, createdate, 111), 1, 7) AS YearMonth, SUM(ContAmt)
FROM Proposals
WHERE createdate >= '5/1/2011 12:00:00 AM' AND createdate < '5/1/2012 12:00:00 AM' AND (IsWin = 1)
GROUP By substring(CONVERT(varchar, createdate, 111), 1, 7)

Query2
SELECT substring(CONVERT(varchar, createdate, 111), 1, 7) AS YearMonth, SUM(ContAmt)
FROM Proposals
WHERE createdate >= '5/1/2011 12:00:00 AM' AND createdate < '5/1/2012 12:00:00 AM'
GROUP By substring(CONVERT(varchar, createdate, 111), 1, 7)

I think I need to divide the result of query2 by query1 while retaining the YearMonth. I am trying to do it without using temp table or creating views. Would it be possible?
thanks!

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

rskepton

ASKER

Thanks Scott! it is exactly what we are looking for!

rskepton

ASKER

Could you also please take a look at the following query, I am trying to also show win ratio by number of proposals instead of contract amount, the total and WinsOnly works but the Percentage always return 0...
thanks in adv

SELECT
substring(CONVERT(varchar, createdate, 111), 1, 7) AS YearMonth,
count(projectID) AS Total,
SUM(CASE WHEN ([STATUS] IN ('3', '4', '5')) THEN 1 ELSE 0 END) AS WinsOnly,
SUM(CASE WHEN ([STATUS] IN ('3', '4', '5')) THEN 1 ELSE 0 END) / count(projectID) AS Percentage
FROM Proposals
WHERE
createdate >= '5/1/2011 12:00:00 AM' AND
createdate < '5/1/2012 12:00:00 AM'
GROUP BY
substring(CONVERT(varchar, createdate, 111), 1, 7)

Scott Pletcher

...
CAST(SUM(CASE WHEN ([STATUS] IN ('3', '4', '5')) THEN 1 ELSE 0 END) * 1.0 / count(projectID) AS decimal(5, 2)) AS Percentage
...

Scott Pletcher

The "* 1.0" is just a little "trick" to force SQL to use a decimal data type for the upcoming divide, to allow an accurate % result.

When SQL divides integers, it uses pure integer arithmetic.

Compare the results below:
SELECT 99/100, 99 * 1.0 / 100

The added CAST(... AS decimal(5, 2)) gets rid of the extra decimal places SQL adds when doing the decimal division :-) .

rskepton

ASKER

man you're good. thanks...