Percentage calculation sql 2005

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

acardulloSystems Administrator. DBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
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
Kevin CrossChief Technology OfficerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
acardulloSystems Administrator. DBAAuthor Commented:
Thank you very much.
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Kevin CrossChief Technology OfficerCommented:
You are most welcome.
0
acardulloSystems Administrator. DBAAuthor 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
0
Kevin CrossChief Technology OfficerCommented:
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
Kevin CrossChief Technology OfficerCommented:
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
acardulloSystems Administrator. DBAAuthor Commented:
THanks again!  Very helpfull information.
0
acardulloSystems Administrator. DBAAuthor 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

0
Kevin CrossChief Technology OfficerCommented:
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
acardulloSystems Administrator. DBAAuthor 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!
0
Kevin CrossChief Technology OfficerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.