Percentage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Percentage

Author

Topic

mike1973
Starting Member

46 Posts

Posted - 2010-09-06 : 12:28:04

Hi guys,

I'm new to SQL development.
I'm trying to get the percentage for each employee returned in the following Query, but not sure how i can do so


ALTER PROCEDURE [dbo].[usp_rr_admin_pc_getPercentageforReport]
@RegionID INT,
@MonthID INT,
@ServiceID INT
WITH EXECUTE AS CALLER
AS


(SELECT DISTINCT AutoID,Users.FirstNameAR,User_Has_Services.CategoryID,(ServiceTypes_Categories.CategoryNameAR) AS ServiceName,
Users.UserID, AgentID, (EMPLOYEES.FirstNameAR + ' ' + EMPLOYEES.LastNameAR) AS EmployeeNameAr , 
Billing_Setups.Fee
	FROM User_Has_Services 
	JOIN ServiceTypes_Categories ON User_Has_Services.CategoryID = ServiceTypes_Categories.CategoryID 
	JOIN Employees ON User_Has_Services.AgentID=Employees.EmployeeID
	JOIN Users on Users.UserID=User_Has_Services.UserID
	JOIN Billing_Setups on Billing_Setups.ServiceCategoryID=ServiceTypes_Categories.CategoryID
	AND ServiceTypes_Categories.ServiceTypeID=1
	AND Users.Active=1
	AND Billing_Setups.MonthID=1
	AND Users.RegionID=1
	AND Users.isFree=0)
EXCEPT
(SELECT DISTINCT AutoID,Users.FirstNameAR,User_Has_Services.CategoryID,(ServiceTypes_Categories.CategoryNameAR) AS ServiceName,
Users.UserID, AgentID, (EMPLOYEES.FirstNameAR + ' ' + EMPLOYEES.LastNameAR) AS EmployeeNameAr , 
Billing_Setups.Fee
	FROM User_Has_Services 
	JOIN ServiceTypes_Categories ON User_Has_Services.CategoryID = ServiceTypes_Categories.CategoryID 
	JOIN Employees ON User_Has_Services.AgentID=Employees.EmployeeID
	JOIN Users on Users.UserID=User_Has_Services.UserID
	JOIN Billing_Setups on Billing_Setups.ServiceCategoryID=ServiceTypes_Categories.CategoryID
	AND ServiceTypes_Categories.ServiceTypeID=1
	AND Users.Active=1
	AND Billing_Setups.MonthID=1
	AND Users.RegionID=1
	AND Users.UserID IN (Select UserID from SEO_BillingsData WHERE BillDateID=1)
	AND Users.isFree=0
	)
ORDER BY EmployeeNameAr

The Query should represents a total number of records, is there a way that i can select a percentage for each EmployeeNameAr for this result?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-06 : 13:31:21

sorry i cant see where you're returning total number here. Is this full code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mike1973
Starting Member

46 Posts

Posted - 2010-09-06 : 14:52:57

quote:
Originally posted by visakh16

sorry i cant see where you're returning total number here. Is this full code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, this is the total code. i didn't know how to count the Employees

mike1973
Starting Member

46 Posts

Posted - 2010-09-07 : 02:48:59

Any help Please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-07 : 02:58:41

Shouldn't this query be enough? Why use EXCEPT?


(SELECT DISTINCT AutoID,Users.FirstNameAR,User_Has_Services.CategoryID,(ServiceTypes_Categories.CategoryNameAR) AS ServiceName,
Users.UserID, AgentID, (EMPLOYEES.FirstNameAR + ' ' + EMPLOYEES.LastNameAR) AS EmployeeNameAr , 
Billing_Setups.Fee
	FROM User_Has_Services 
	JOIN ServiceTypes_Categories ON User_Has_Services.CategoryID = ServiceTypes_Categories.CategoryID 
	JOIN Employees ON User_Has_Services.AgentID=Employees.EmployeeID
	JOIN Users on Users.UserID=User_Has_Services.UserID
	JOIN Billing_Setups on Billing_Setups.ServiceCategoryID=ServiceTypes_Categories.CategoryID
	AND ServiceTypes_Categories.ServiceTypeID=1
	AND Users.Active=1
	AND Billing_Setups.MonthID=1
	AND Users.RegionID=1
        AND Users.UserID NOT IN (Select UserID from SEO_BillingsData WHERE BillDateID=1)
	AND Users.isFree=0)
EXCEPT
(SELECT DISTINCT AutoID,Users.FirstNameAR,User_Has_Services.CategoryID,(ServiceTypes_Categories.CategoryNameAR) AS ServiceName,
Users.UserID, AgentID, (EMPLOYEES.FirstNameAR + ' ' + EMPLOYEES.LastNameAR) AS EmployeeNameAr , 
Billing_Setups.Fee
	FROM User_Has_Services 
	JOIN ServiceTypes_Categories ON User_Has_Services.CategoryID = ServiceTypes_Categories.CategoryID 
	JOIN Employees ON User_Has_Services.AgentID=Employees.EmployeeID
	JOIN Users on Users.UserID=User_Has_Services.UserID
	JOIN Billing_Setups on Billing_Setups.ServiceCategoryID=ServiceTypes_Categories.CategoryID
	AND ServiceTypes_Categories.ServiceTypeID=1
	AND Users.Active=1
	AND Billing_Setups.MonthID=1
	AND Users.RegionID=1
	AND Users.UserID IN (Select UserID from SEO_BillingsData WHERE BillDateID=1)
	AND Users.isFree=0
	)
ORDER BY EmployeeNameAr

N 56°04'39.26"
E 12°55'05.63"

mike1973
Starting Member

46 Posts

Posted - 2010-09-07 : 03:02:16

right, but how do i get percentage for each employee returned in this query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-07 : 05:30:31

First you have to explain WHICH percentage?
Percentage of time spent in a category? Total?
Percentage of incidents? Compared to what?

As you see, we can't help you until you clearly explain what percentage calculation you want.
I do hope you know that a percentage only is a rate or quote normalised by the hundreds?

N 56°04'39.26"
E 12°55'05.63"

mike1973
Starting Member

46 Posts

Posted - 2010-09-07 : 07:21:39

I'm trying to calculate the percentage of each employee compared to the total records returned by this query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-07 : 09:53:43

Add this line to the query

, 100.0 * COUNT(*) OVER (Employees.EmployeeID) / COUNT(*) OVER ()

N 56°04'39.26"
E 12°55'05.63"

mike1973
Starting Member

46 Posts

Posted - 2010-09-07 : 10:06:37

this is the new query

SELECT DISTINCT AutoID,Users.FirstNameAR,User_Has_Services.CategoryID,(ServiceTypes_Categories.CategoryNameAR) AS ServiceName,
Users.UserID, AgentID, (EMPLOYEES.FirstNameAR + ' ' + EMPLOYEES.LastNameAR) AS EmployeeNameAr ,
Billing_Setups.Fee, 100.0 * COUNT(*) OVER (Employees.EmployeeID) / COUNT(*) OVER ()
FROM User_Has_Services
JOIN ServiceTypes_Categories ON User_Has_Services.CategoryID = ServiceTypes_Categories.CategoryID
JOIN Employees ON User_Has_Services.AgentID=Employees.EmployeeID
JOIN Users on Users.UserID=User_Has_Services.UserID
JOIN Billing_Setups on Billing_Setups.ServiceCategoryID=ServiceTypes_Categories.CategoryID
AND ServiceTypes_Categories.ServiceTypeID=1
AND Users.Active=1
AND Billing_Setups.MonthID=1
AND Users.RegionID=1
AND Users.UserID NOT IN (Select UserID from SEO_BillingsData WHERE BillDateID=1)
AND Users.isFree=0
ORDER BY EmployeeNameAr

i am getting the following errors:

Msg 102, Level 15, State 1, Procedure usp_rr_admin_pc_getPercentageforReport, Line 11
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Procedure usp_rr_admin_pc_getPercentageforReport, Line 22
Incorrect syntax near the keyword 'AND'.

mike1973
Starting Member

46 Posts

Posted - 2010-09-08 : 01:58:57

any help please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-08 : 03:01:16

[code]SELECT DISTINCT AutoID,
Users.FirstNameAR,
User_Has_Services.CategoryID,
ServiceTypes_Categories.CategoryNameAR AS ServiceName,
Users.UserID,
AgentID,
EMPLOYEES.FirstNameAR + ' ' + EMPLOYEES.LastNameAR AS EmployeeNameAr ,
Billing_Setups.Fee,
100.0 * COUNT(*) OVER (PARTITION BY Employees.EmployeeID) / COUNT(*) OVER ()
FROM User_Has_Services
INNER JOIN ServiceTypes_Categories ON User_Has_Services.CategoryID = ServiceTypes_Categories.CategoryID
AND ServiceTypes_Categories.ServiceTypeID = 1
INNER JOIN Employees ON User_Has_Services.AgentID = Employees.EmployeeID
INNER JOIN Users on Users.UserID = User_Has_Services.UserID
AND Users.Active = 1
AND Users.RegionID = 1
AND Users.UserID NOT IN (Select UserID from SEO_BillingsData WHERE BillDateID=1)
AND Users.isFree = 0
INNER JOIN Billing_Setups on Billing_Setups.ServiceCategoryID = ServiceTypes_Categories.CategoryID
AND Billing_Setups.MonthID=1
ORDER BY EmployeeNameAr[/code]

N 56°04'39.26"
E 12°55'05.63"

mike1973
Starting Member

46 Posts

Posted - 2010-09-08 : 03:57:20

Hi Peso,

i'm still getting the same errors that's weird

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'AND'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-08 : 04:15:10

I edited my response above.

N 56°04'39.26"
E 12°55'05.63"

mike1973
Starting Member

46 Posts

Posted - 2010-09-08 : 04:19:31

Thanks a LOTTTTTTT it worked mate, you're the best.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources