Solved

Percentage calculation sql 2005

Posted on 2009-07-09
12
204 Views
Last Modified: 2012-05-07
The sp below tells me the percentages of service calls that have symptom code PHONE.
Id like to see perentage of all ocuring symptoms. How would i have to alter this code to achieve that?

thank you

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
--exec [dbo].[spr_PhoneFix90days]
-- =============================================
 
ALTER PROCEDURE [dbo].[spr_PhoneFix90days]
	-- Add the parameters for the stored procedure here
	
AS
 
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET ROWCOUNT 0
	SET NOCOUNT ON;
DECLARE 
  @Count   int,
  @PhoneCount    int,
  @PhonePercent decimal(10,1)
  
  SELECT @Count = (SELECT COUNT( *) FROM root_SERV_CALL)
  SELECT @PhoneCount = (SELECT COUNT( *) FROM root_SERV_CALL WHERE SYMPTOM_CODE = 'PHONE')
  
	SET @PhonePercent = cast(@PhoneCount as decimal(10,1))/ cast(@Count as decimal(10,1)) * 100
	--SET @PhonePercent = (@PhoneCount/@Count)*100
	--SET @PhonePercent = ((@Count * 100)/@PhoneCount)  
	--SET @PhonePercent =  @PhoneCount / @Count)
   --SET @PhonePercent =  (@PhoneCount / @Count) * 100
--PRINT @Phonecount
 
    -- Insert statements for procedure here
	SELECT     MODEL, SERIAL, CALL_DATE_SCA, REPRESENTATIVE, CALL_ID, CALL_TYPE, SYMPTOM_CODE, PRODUCT, @PhonePercent AS PhonePercent, 
                      @PhoneCount AS PhoneCount, @Count AS RCount
FROM         root_SERV_CALL
WHERE     (SYMPTOM_CODE = N'PHONE') AND (CALL_DATE_SCA > DATEADD(day, - 90, GETDATE()))
--(SYMPTOM_CODE = N'PHONE') AND 
END

Open in new window

0
Comment
Question by:acardullo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24816595
Maybe something like this works for you:
SELECT  sc.MODEL, sc.SERIAL, sc.CALL_DATE_SCA, sc.REPRESENTATIVE
, sc.CALL_ID, sc.CALL_TYPE, sc.SYMPTOM_CODE, sc.PRODUCT
, CAST(c.CodeCount * 100.0 / t.RCount AS DECIMAL(10,1)) AS CodePercent, c.CodeCount, t.RCount
FROM    root_SERV_CALL sc
CROSS APPLY (SELECT COUNT(*) As CodeCount FROM root_SERV_CALL scB WHERE scB.SYMPTOM_CODE = sc.SYMPTOM_CODE) c
OUTER APPLY (SELECT COUNT(*) AS RCount FROM root_SERV_CALL) t
WHERE  	CALL_DATE_SCA > DATEADD(day, - 90, GETDATE())

Open in new window

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24816656
CROSS APPLY is just fancy SQL 2005 way of doing this:

root_SERV_CALL sc
INNER JOIN (
   SELECT SYMPTOM_CODE, COUNT(*) As CodeCount FROM root_SERV_CALL GROUP BY SYMPTOM_CODE
) scB ON scB.SYMPTOM_CODE = sc.SYMPTOM_CODE

OUTER APPLY does similar, just puts result for each row without having to match.  Can also do this with a subquery in the select, but used OUTER APPLY to only have to define query once and use column name versus having to repeat subquery for count and percentage formula.

HTH

Kevin
0
 

Author Comment

by:acardullo
ID: 24817512
Thank you very much.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24817540
You are most welcome.
0
 

Author Comment

by:acardullo
ID: 24817909
Just one more question.  What if i wanted to supply the sp wiht a list of Symptoms instead of it goign through every possible symptom value.  like   PHONE, Elec, Print  etc
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24818011
You would just use a WHERE clause on SYMPTOM:
(if you want to pass in the parameter as a comma delimited string, then see this solution by Angel Eyes on creating a parmsToList function that you can then use in proc -- http:/Q_21627393.html#20522680)
SELECT  sc.MODEL, sc.SERIAL, sc.CALL_DATE_SCA, sc.REPRESENTATIVE
, sc.CALL_ID, sc.CALL_TYPE, sc.SYMPTOM_CODE, sc.PRODUCT
, CAST(c.CodeCount * 100.0 / t.RCount AS DECIMAL(10,1)) AS CodePercent, c.CodeCount, t.RCount
FROM    root_SERV_CALL sc
CROSS APPLY (SELECT COUNT(*) As CodeCount FROM root_SERV_CALL scB WHERE scB.SYMPTOM_CODE = sc.SYMPTOM_CODE) c
OUTER APPLY (SELECT COUNT(*) AS RCount FROM root_SERV_CALL) t
WHERE   sc.CALL_DATE_SCA > DATEADD(day, - 90, GETDATE())
   AND sc.SYMPTOM_CODE IN ('PHONE', 'Elec', 'Print')

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24818051
It is the bottom most post by angelIII with ID 20522680.  
You can also see http:/Q_23786715.html for solutions by Brandon Galderisi and myself.

The net you will see is that this:
sc.SYMPTOM_CODE IN ('PHONE', 'Elec', 'Print')

Converts to this:
sc.SYMPTOM_CODE IN (SELECT string_value FROM dbo.your_split_function(@csvParameter, ','))

Or something close as you may chose to hardcode the delimiter in yours to make the call to function easier where as we left function open to use for different scenarios.
0
 

Author Comment

by:acardullo
ID: 24822161
THanks again!  Very helpfull information.
0
 

Author Comment

by:acardullo
ID: 24822601
Ok  im confused.
If i wanted to list every Representative and theyr respective % per call type in a report matrix how would i have to format the procedure?
Plkease see code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
--exec [dbo].[usp_FirstCallEffectiveness90Days]
-- =============================================
 
ALTER PROCEDURE [dbo].[usp_FirstCallEffectiveness90Days]
	-- Add the parameters for the stored procedure here
	
AS
 
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET ROWCOUNT 0
	SET NOCOUNT ON;
DECLARE 
  @Count   int,
  @TypeCount    int,
  @Percentage decimal(10,1)
  
  SELECT @Count = (SELECT COUNT( *) FROM root_SERV_CALL)
  SELECT @TypeCount = (SELECT COUNT( *) FROM root_SERV_CALL WHERE CALL_TYPE = 'F')
  
	SET @Percentage = cast(@TypeCount as decimal(10,1))/ cast(@Count as decimal(10,1)) * 100
	
--PRINT @TypeCount
 
   
SELECT  sc.MODEL, sc.SERIAL, sc.OPEN_DATE_SCA, sc.REPRESENTATIVE
, sc.CALL_ID, sc.CALL_TYPE, sc.SYMPTOM_CODE, sc.PRODUCT
, CAST(c.CodeCount * 100.0 / t.RCount AS DECIMAL(10,1)) AS PERC, c.CodeCount, t.RCount
FROM    root_SERV_CALL sc
CROSS APPLY (SELECT COUNT(*) As codeCount FROM root_SERV_CALL scB WHERE scB.CALL_TYPE = sc.CALL_TYPE) c
OUTER APPLY (SELECT COUNT(*) AS RCount FROM root_SERV_CALL) t
WHERE  	OPEN_DATE_SCA > DATEADD(day, - 90, GETDATE()) --AND REPRESENTATIVE = 'TA0039'
END

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24823964
Is that a whole new question/requirement?  I don't remember anything about representative before.  That may change it if for example you want the total calls to be by rep and then same for symptom count.  I would post in a new question the current cleaned up procedure with my fix above as well as handling the CSV parameter for list of symptoms.  Then clearly define the how you want the data split up/calculated per rep.  If I am understanding correctly, you can probably just adjust what I showed you using CROSS APPLY to also filter on rep; however, you will get other experts perspectives in a new question.
0
 

Author Comment

by:acardullo
ID: 24823989
You are right.  this wouldve been a new question. i just solved it myself by grouping the results at the Reporting level.  Thank you!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24824131
Glad you found it.  

And like I said, maybe other ways, but you can update my CROSS APPLY to not only check symptom matches current record, but also representative.

Happy coding!

Best regards,
Kevin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question