May 12, 2011 at 1:13 pm
I would like to calculate a Percentage by each EmpID with a UNIQUE Division and DEPT
Here is my table with sample data.
EmpId Division Department Salary PayDate
10 West ADMIN $1000 1/1/2011
10 West ADMIN $1000 1/15/2011
10 West ADMIN $1000 1/30/2011
20 East Sales $1000 1/1/2011
20 East Sales $1000 1/15/2011
20 North PR $1000 1/30/2011
20 North PR $1000 2/15/2011
30 South ADMIN $1000 1/1/2011
30 South ADMIN $1000 1/15/2011
30 West HR $1000 1/30/2011
30 East ADMIN $1000 2/15/2011
Desired Result (Records are summarized to include EMPID with unique Division and Department) and the percentage is based on "each EmpId's" Division/Dept relative salary.
EmpId Division Department Salary Percentage
10 West ADMIN $3000 100%
20 East Sales $2000 50%
20 North PR $2000 50%
30 South ADMIN $2000 50%
30 West HR $1000 25%
30 East ADMIN $1000 25%
Thank you in advance for your assistance
May 12, 2011 at 1:33 pm
This work?
SELECT s1.EmpId, s1.Division, s1.Department, SUM(s1.Salary), SUM(s1.Salary)/s2.Salary
FROM SomeTable s1
INNER JOIN (SELECT EmpId, SUM(Salary) AS Salary FROM SomeTable GROUP BY EmpId) s2
ON s1.EmpId = s2.EmpId
GROUP BY s1.EmpId, s1.Division, s1.Department, s2.Salary
_____________________________________________________________________
- Nate
May 12, 2011 at 2:04 pm
Don't you really want to group Division & Department and then list the EmpId, Sum of the Salary, and a calulated Percentage?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 2:36 pm
Thanks it works but the only problem is if I select records by only certain date, the percentages are off.
It seems to taking percentage of all records.
May 12, 2011 at 2:38 pm
Actually it would not make any difference to me, so long the empid, division, dept are unique.
I would also like to filter the records by certain date.
thanks
May 12, 2011 at 2:40 pm
What is the formula for percentage?
Don't you want your records to be Grouped By and Ordered by Division & Department so that they are aggregated and Sorted in that order?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 3:12 pm
I was able to make this work.
Many Thanks!!!!!!
May 12, 2011 at 3:19 pm
Awesome and you came up with the code on your own!
Please post the solution.
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 10:34 pm
the solution is the same as posted by Nate. I just added a date filter to both Select Statements and it worked.
May 13, 2011 at 3:39 pm
I guess I still have SQL2sDay on my mind:
; with empTotSal (EmpID, TotSal) as
(Select EmpID
, SUM(Salary)
from forumPct
group by EmpID)
SELECT fp.EmpID
, fp.Division
, fp.Department
, SUM(fp.Salary) as DivSal
, SUM(fp.Salary) * 100 / ets.TotSal as Pct
FROM forumPct fp
join empTotSal ets on fp.EmpID = ets.EmpID
group by fp.EmpID
, fp.Division
, fp.Department
, ets.TotSal
order by fp.EmpID
, (SUM(fp.Salary) / ets.TotSal) desc
, fp.Division
, fp.Department
If you're using date filters, they would have to go into both the CTE and the main query.
This works, but (asking this as a newbie) is it acceptable?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply