November 22, 2011 at 12:50 pm
How can I get the percentage Quotes that became a Sale using a CTE?
SELECT TOP 5 [Marketing Rep], MarketingTerritoryName,OrgTypeDescription, SUM(Quote) AS Quote, SUM(Sale) AS Sale
FROM QuoteActivity
GROUP BY [Marketing Rep], MarketingTerritoryName,OrgTypeDescription
Marketing RepMarketingTerritoryNameAgencyTypeDescriptionQuoteSale
Shallow HalCentral Market Solutions Group158 3
Shallow HalSoutheast Independent 36 4
Shallow HalSouthwest EggHead 613140
Shallow HalSouthwest Select Independents 35 2
Shallow HalSouthwest Fiesta 116 12
Any help would be greatly appreciated
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2011 at 1:03 pm
Going to need to back this up a bit. How do you know if something became a sale from the raw data?
Or are you looking for an in-line percentage like SUM(SAle)/SUM(Quote) AS PercentageConverted?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2011 at 1:13 pm
Craig,
I'm looking for the SUM(Sale)/SUM(Quote) WHERE Sale could be zero.
Thanks!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2011 at 1:20 pm
100.00 * Sum(Sale)/NULLIF(Sum(Quote), 0)
This makes sure that you are not performing integer division and also produces a null result when Sum(Quote) is zero.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 22, 2011 at 1:40 pm
Awesome.
I'm trying to get the result rounded and Converted as follows:
No. of QuotesNo. Sold% Sold
140 3 2%
140 3 2%
36 4 11%
36 4 11%
599 135 23%
35 2 6%
18,409 4,364 24%
11 0 0%
328 52 16%
959 192 20%
347 41 12%
106 9 8%
20,794 4,795 23%
20,970 4,802 23%
2 0 0%
2 0 0%
76 4 5%
76 4 5%
78 4 5%
1 1100%
25 1 4%
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2011 at 1:56 pm
The following does not right justify the percentage:
CAST(CONVERT(Decimal(9,2),ROUND(100.00 * Sum(Sale)/NULLIF(Sum(Quote), 0) ,2)) AS VARCHAR(6)) + '%'
NoSales
1.90%
11.11%
22.80%
5.71%
10.34%
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2011 at 2:31 pm
Welsh Corgi (11/22/2011)
The following does not right justify the percentage:
Justification is a presentation issue and should be handled by the presentation layer not in T-SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 22, 2011 at 2:56 pm
Thanks for the formula.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 23, 2011 at 8:20 am
drew.allen (11/22/2011)
Welsh Corgi (11/22/2011)
The following does not right justify the percentage:Justification is a presentation issue and should be handled by the presentation layer not in T-SQL.
Drew
No hate mail please.:hehe:
I thought that the following was interesting as listed in Jeff Moden's article that you posted in another thread: http://www.sqlservercentral.com/Forums/Topic1210948-338-1.aspx#bm1211028
http://www.sqlservercentral.com/articles/T-SQL/63681/
For most applications, that's good enough. If it's supposed to represent the final output,
we might want to make it a little prettier.
The STR function inherently right justifies, so we can use that to make the output a little prettier.
Please, no hate mail here! I'll be one of the first that formatting of this nature is supposed to be done in the GUI!
--===== We can use the STR function to right justify data and make it prettier.
-- Note that this should really be done by the GUI or Reporting Tool and
-- not in T-SQL
SELECT Year, STR(SUM(CASE WHEN Quarter = 1 THEN Amount
ELSE 0 END),5,1) AS [1st Qtr],
STR(SUM(CASE WHEN Quarter = 2 THEN Amount
ELSE 0 END),5,1) AS [2nd Qtr],
STR(SUM(CASE WHEN Quarter = 3 THEN Amount
ELSE 0 END),5,1) AS [3rd Qtr],
STR(SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END),5,1) AS [4th Qtr],
STR(SUM(Amount),5,1) AS Total
FROM #SomeTable1 GROUP BY Year
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy