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