GROUPING ISSUE - 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

SQL Server 2000 Forums

Transact-SQL (2000)

GROUPING ISSUE

Author

Topic

obiwaugh
Starting Member

27 Posts

Posted - 2004-12-13 : 13:35:56

I've grouped by LOCATION, JOBCODE to get the following data. #EMPS counts the number of distinct employees. TERMS counts then number of terminated employees. TURNOVER divides TERMS by #EMPS.

How can I add up the number of employees in the location? Then, I would like to add up the number of terms in the location? Then, as you might expect, I'd like to divide one by the other to get the turnover rate for the entire location.

Help!

LOCATION JOBCODE #EMPS TERMS TURNOVER
MDCTR 1001 2 0 0.00
MDCTR 1003 5 0 0.00
MDCTR 1004 19 4 0.21
MDCTR 1007 1 0 0.00
MDCTR 1014 3 1 0.33
MDCTR 1015 1 0 0.00

Code used for above results:

SELECT
A.LOCATION,A.JOBCODE,
COUNT(DISTINCT EMPLID) AS '#EMPS',
SUM (
CASE A.ACTION
WHEN 'TER' THEN 1.00
ELSE 0
END
) AS 'TERMS',
(
SUM (
CASE A.ACTION
WHEN 'TER' THEN 1.00
ELSE 0
END
)
)
/
(COUNT (DISTINCT EMPLID)) AS 'TURNOVER'

FROM DEMOV3.dbo.PS_JOB A
WHERE (LOCATION = 'MDCTR') AND (A.EFFDT BETWEEN
(
SELECT MAX(B.EFFDT)
FROM DEMOV3.dbo.PS_JOB B
WHERE A.EMPLID=B.EMPLID
AND B.EFFDT <= '01/01/2001'
)
AND
(
SELECT MAX(C.EFFDT)
FROM DEMOV3.dbo.PS_JOB C
WHERE A.EMPLID=C.EMPLID
AND C.EFFDT <= '12/31/2001'))
GROUP BY
A.LOCATION,A.JOBCODE
ORDER BY
A.LOCATION,A.JOBCODE

obiwaugh
Starting Member

27 Posts

Posted - 2004-12-13 : 14:38:34

I figured it out.....I just did a subquery and grouped by location only. Not that hard now that I think of it.

I know enough to know that I don't know enough.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources