rskepton

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!

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,

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,

Query2

SELECT substring(CONVERT(varchar,

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,

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

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**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)

thanks in adv

SELECT

substring(CONVERT(varchar,

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,

...

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

...

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

...

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 :-) .

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 :-) .

ASKER

man you're good. thanks...

ASKER