Solved

Percentage calculation sql 2005

Posted on 2009-07-09
12
199 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.​
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now