jackjohnson44
asked on
SQL calculate years from view
I have a view that has a
EmployeeNumber, companyname, startdate, and enddate
I need to find a way to calculate years of experience for each employeenumber when they worked at the company MYCOMPANY, and also a total of all years worked at another company.
I don't care it this ends up being two rows in the same view for each employeenumber (only one if they don't have other experience), or two different views.
SELECT EmpNo, CompanyName, Location, Title, StartDate, EndDate
FROM dbo.EmpWorkHistory AS e
GROUP BY EmpNo, StartDate, EndDate, CompanyName, Location, Title
ORDER BY EmpNo, StartDate
so
1, Google, 1990-1-1, 1991-1-1
1, IBM, 1991-1-1, 1992-1-1
1, XXX, 1992-1-1, 1993-1-1
1, MyCompany, 1993-1-1, 1994-1-1
would give me
1, MyCompany, 1 Year
1, AllOtherCompanies, 3 Years
Thanks!
EmployeeNumber, companyname, startdate, and enddate
I need to find a way to calculate years of experience for each employeenumber when they worked at the company MYCOMPANY, and also a total of all years worked at another company.
I don't care it this ends up being two rows in the same view for each employeenumber (only one if they don't have other experience), or two different views.
SELECT EmpNo, CompanyName, Location, Title, StartDate, EndDate
FROM dbo.EmpWorkHistory AS e
GROUP BY EmpNo, StartDate, EndDate, CompanyName, Location, Title
ORDER BY EmpNo, StartDate
so
1, Google, 1990-1-1, 1991-1-1
1, IBM, 1991-1-1, 1992-1-1
1, XXX, 1992-1-1, 1993-1-1
1, MyCompany, 1993-1-1, 1994-1-1
would give me
1, MyCompany, 1 Year
1, AllOtherCompanies, 3 Years
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.