Solved

Percentage calculation sql 2005

Posted on 2009-07-09
12
202 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

860 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