Need an average in years by SysOrgSID

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)

Need an average in years by SysOrgSID

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-03-17 : 11:27:26

This is what I have so far

SELECT DATEDIFF(Year, dbo.tblDIMHREmployee.HREmpUniversityHireDate, GETDATE()) AS Expr1, dbo.tblDIMSystemOrganization.SysOrgSID
FROM dbo.tblDIMHREmployee INNER JOIN
dbo.tblDIMSystemOrganization ON dbo.tblDIMHREmployee.SysOrgSID = dbo.tblDIMSystemOrganization.SysOrgSID
WHERE (dbo.tblDIMHREmployee.HREmpUniversityHireDate IS NOT NULL) AND (dbo.tblDIMHREmployee.HREmpRetireDate IS NULL)
ORDER BY dbo.tblDIMHREmployee.HREmpUniversityHireDate

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 11:54:08

Don't you need a GROUP BY?

What class is this for?

EDIT And don't worry about WHERE the Date is not null


    SELECT o.SysOrgSID 
	 , AVG(DATEDIFF(dd, e.HREmpUniversityHireDate, GETDATE())/360.00) AS AVG_YearsHired
      FROM tblDIMHREmployee e
INNER JOIN tblDIMSystemOrganization o
	ON e.SysOrgSID = o.SysOrgSID
     WHERE e.HREmpRetireDate IS NULL
GROUP BY o.SysOrgSID

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources