Hello, 

I am writing some reports for a quotation system and I am having troubles 
getting the data I need.  I am using SQL Server 2000 and Crystal 

Reports 8.  I'm trying to do most of the work within SQL Server.  If you 
could help me that would be great!

I have created a view which contains summary information of all quotes and 
the geographical regions they are in.  The structure is:

vwrptQuotesInRegions
-------------------------------
QuoteID
TotalPrice
DateReleased
QuotationStatusCodeID 
RegionCode          
------------------------------

I have also created a view representing just the regions.  The structure 
is:

vwrptRegions
----------------------------
RegionCode
Description
----------------------------

I need to extract:
	1. How many quotes have been won in the current year
	2. What percentage of quotes have been won in relation to all 
quotes in the year.

I can return the first item easily enough with the following statement 
(QuotationStatusCodeID = 5 is WON):
_______________________________________________
SELECT		R.RegionCode,
		Count(Q.QuoteID) As QuotesWon
FROM		vwrptQuotesInRegions Q
RIGHT JOIN	vwrptRegions R
ON		R.RegionCode = Q.RegionCode
		AND Year(Q.DateReleased) = 2001
		AND QuotationStatusCodeID = 5
GROUP BY	R.RegionCode
ORDER BY	R.RegionCode
_______________________________________________

Which returns the following data:

RegionCode     	     QuotesWon   
-------------------- ----------- 
ACT                  0
NSW                  0
QLD                  0
TAS                  2
VIC                  4
WA                   0
_______________________________________________


I can also return all quotes in a given year no matter what status by 
omitting the 'AND QuotationStatusCodeID = 5' clause in the previous 

query.  EG.
_______________________________________________
SELECT		R.RegionCode,
		Count(Q.QuoteID) As TotalQuotes
FROM		vwrptQuotesInRegions Q
RIGHT JOIN	vwrptRegions R
ON		R.RegionCode = Q.RegionCode
		AND Year(Q.DateReleased) = 2001
GROUP BY	R.RegionCode
ORDER BY	R.RegionCode
______________________________________________

Which returns the following data:

RegionCode           TotalQuotes 
-------------------- ----------- 
ACT                  0
NSW                  0
QLD                  0
TAS                  5
VIC                  11
WA                   0


I also know what calculation needs to performed to get the percentage of 
quotes won: (QuotesWon / TotalQuotes) * 100

What I don't know how to do is perform both these things at the same time 
to get results that look like this:

RegionCode     TotalQuotes  QuotesWon       PercentageWon
-------------- ------------ ----- 	    -------------
ACT            0	    0		    0%
NSW            0	    0		    0%
QLD            0	    0		    0%
TAS            5	    2		    40%
VIC            11	    4		    36%
WA             0	    0		    0%

My thoughts are (apart from the fact that I need to be better at SQL) that 
I need to use subqueries in the SELECT to pull it all together, 

but I have no idea really.
Any help would be appreciated.  By email would be great.
Thanks a lot.
Nicholas Trevatt
nicholas@t...