Solved

Percentage calculation sql 2005

Posted on 2009-07-09
12
203 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
  • 7
  • 5
12 Comments
 
LVL 59

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 59

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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 59

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 59

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 59

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 59

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 59

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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