We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Percentage calculation sql 2005

Anthony Cardullo
on
Medium Priority
219 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

Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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

Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Anthony CardulloSystem Administrator

Author

Commented:
Thank you very much.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
You are most welcome.
Anthony CardulloSystem Administrator

Author

Commented:
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
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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.
Anthony CardulloSystem Administrator

Author

Commented:
THanks again!  Very helpfull information.
Anthony CardulloSystem Administrator

Author

Commented:
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

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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.
Anthony CardulloSystem Administrator

Author

Commented:
You are right.  this wouldve been a new question. i just solved it myself by grouping the results at the Reporting level.  Thank you!
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.