June 8, 2012 at 7:15 am
Is it possible to have this query do a distinct count of each orglevel?
SELECT
----DATEPART(YEAR, e.eecDateOfLastHire) as 'Year of Hire',
--EEPDATEOFBIRTH,
(select distinct count E.EecOrgLvl2) as 'county',
CmpCompanyCode as 'Company Code',
E.EecOrgLvl2 as 'Org Level',
cmpCompanyName as 'Company Name',
E.eecCoID as 'Co ID',
E.EecEEID as 'EE ID', --stays with person, if listed more then once
E.eecDateOfTermination as 'Term Date',
E.eecEmplStatus as 'Emp Status',
E.eecEmpNo as 'Emp Number',
E.eecLocation as 'Location',
E.eecFullTimeorPartTime as 'Full or Part',
E.eecJobCode as 'Job Code',
eepNameLast as 'Last Name',
eepNameSuffix as 'Suffix',
eepNameFirst as 'First Name',
EepNameMiddle as 'Middle Name',
E.EecJobtitle as 'Job Title',
E.EecAnnSalary as 'Annual Salary',
E.EecDateLastPayDatePaid as 'Last Pay Date',
E.EecTermReason as 'Term Code'
FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID
JOIN Company ON eecCoID = cmpCoID
WHERE EecDateOfTermination IS NOT NULL
and not exists (select 1 from EmpComp e2 where e2.EecEEID = E.EecEEID and e2.eecEmplStatus = 'A')-- this is to filter out anyone that was termed then re-hired
AND E.EecTermReason not in ('I01','I02','I03','I14','I22','V05','V07','V09','V12','V22','V13')
AND E.eecDateOfTermination in (SELECT MAX(EC.eecDateOfTermination) FROM EMPCOMP EC WHERE EC.EecEEID = E.EECEEID AND (eC.eecDateOfTermination <= '12/31/2011' AND (EC.eecDateOfTermination is not null OR EC.eecDateOfTermination >= '01/01/2011')))
--AND DATEPART(YEAR, e.eecDateOfLastHire) = '2010'
AND DATEPART(YEAR, e.eecDateOfTermination) = '2011'
June 8, 2012 at 7:53 am
Probably.
You've been here long enough to know the score - please provide sample input and output data etc etc.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 8, 2012 at 8:45 am
krypto69 (6/8/2012)
Is it possible to have this query do a distinct count of each orglevel?
SELECT----DATEPART(YEAR, e.eecDateOfLastHire) as 'Year of Hire',
--EEPDATEOFBIRTH,
(select distinct count E.EecOrgLvl2) as 'county',
CmpCompanyCode as 'Company Code',
E.EecOrgLvl2 as 'Org Level',
cmpCompanyName as 'Company Name',
E.eecCoID as 'Co ID',
E.EecEEID as 'EE ID', --stays with person, if listed more then once
E.eecDateOfTermination as 'Term Date',
E.eecEmplStatus as 'Emp Status',
E.eecEmpNo as 'Emp Number',
E.eecLocation as 'Location',
E.eecFullTimeorPartTime as 'Full or Part',
E.eecJobCode as 'Job Code',
eepNameLast as 'Last Name',
eepNameSuffix as 'Suffix',
eepNameFirst as 'First Name',
EepNameMiddle as 'Middle Name',
E.EecJobtitle as 'Job Title',
E.EecAnnSalary as 'Annual Salary',
E.EecDateLastPayDatePaid as 'Last Pay Date',
E.EecTermReason as 'Term Code'
FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID
JOIN Company ON eecCoID = cmpCoID
WHERE EecDateOfTermination IS NOT NULL
and not exists (select 1 from EmpComp e2 where e2.EecEEID = E.EecEEID and e2.eecEmplStatus = 'A')-- this is to filter out anyone that was termed then re-hired
AND E.EecTermReason not in ('I01','I02','I03','I14','I22','V05','V07','V09','V12','V22','V13')
AND E.eecDateOfTermination in (SELECT MAX(EC.eecDateOfTermination) FROM EMPCOMP EC WHERE EC.EecEEID = E.EECEEID AND (eC.eecDateOfTermination <= '12/31/2011' AND (EC.eecDateOfTermination is not null OR EC.eecDateOfTermination >= '01/01/2011')))
--AND DATEPART(YEAR, e.eecDateOfLastHire) = '2010'
AND DATEPART(YEAR, e.eecDateOfTermination) = '2011'
I have to ask, distinct count of what?
June 8, 2012 at 8:48 am
Lynn Pettis (6/8/2012)
krypto69 (6/8/2012)
Is it possible to have this query do a distinct count of each orglevel?
SELECT----DATEPART(YEAR, e.eecDateOfLastHire) as 'Year of Hire',
--EEPDATEOFBIRTH,
(select distinct count E.EecOrgLvl2) as 'county',
CmpCompanyCode as 'Company Code',
E.EecOrgLvl2 as 'Org Level',
cmpCompanyName as 'Company Name',
E.eecCoID as 'Co ID',
E.EecEEID as 'EE ID', --stays with person, if listed more then once
E.eecDateOfTermination as 'Term Date',
E.eecEmplStatus as 'Emp Status',
E.eecEmpNo as 'Emp Number',
E.eecLocation as 'Location',
E.eecFullTimeorPartTime as 'Full or Part',
E.eecJobCode as 'Job Code',
eepNameLast as 'Last Name',
eepNameSuffix as 'Suffix',
eepNameFirst as 'First Name',
EepNameMiddle as 'Middle Name',
E.EecJobtitle as 'Job Title',
E.EecAnnSalary as 'Annual Salary',
E.EecDateLastPayDatePaid as 'Last Pay Date',
E.EecTermReason as 'Term Code'
FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID
JOIN Company ON eecCoID = cmpCoID
WHERE EecDateOfTermination IS NOT NULL
and not exists (select 1 from EmpComp e2 where e2.EecEEID = E.EecEEID and e2.eecEmplStatus = 'A')-- this is to filter out anyone that was termed then re-hired
AND E.EecTermReason not in ('I01','I02','I03','I14','I22','V05','V07','V09','V12','V22','V13')
AND E.eecDateOfTermination in (SELECT MAX(EC.eecDateOfTermination) FROM EMPCOMP EC WHERE EC.EecEEID = E.EECEEID AND (eC.eecDateOfTermination <= '12/31/2011' AND (EC.eecDateOfTermination is not null OR EC.eecDateOfTermination >= '01/01/2011')))
--AND DATEPART(YEAR, e.eecDateOfLastHire) = '2010'
AND DATEPART(YEAR, e.eecDateOfTermination) = '2011'
I have to ask, distinct count of what?
Is there an index on eecDateOfTermination column on the table EmpComp? Because right now, if there is, your query won't use it. You should change this:
AND DATEPART(YEAR, e.eecDateOfTermination) = '2011'
to this:
AND e.eecDateOfTermination >= '20110101' and e.eecDateOfTermination < '20120101'
June 8, 2012 at 8:54 am
yes
select count(distinct orglevel)
,x
,y
,z
from ...
where x=a
group by
x
,y
,z
MVDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply