# 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, 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!
###### Who is Participating?

Data and Analytics ManagerCommented:
Is this what you want?

``````SELECT CASE Company WHEN 'MyCompany' THEN 'MyCompany' ELSE 'AllOtherCompanies' END as Company,
SUM(DATEDIFF(yy, StartDate, EndDate)) as YearsExperience
FROM dbo.EmpWorkHistory AS e
GROUP BY CASE Company WHEN 'MyCompany' THEN 'MyCompany' ELSE 'AllOtherCompanies' END
``````
0

Data and Analytics ManagerCommented:
Wow - I don't know what I was thinking - that's not at all what you want. Here's what you're looking for, and can do it in a single row per employee:

``````SELECT EmpNo, SUM(CASE Company WHEN 'MyCompany' THEN DATEDIFF(yy, StartDate, EndDate) ELSE 0 END) as YearsWithUs,
SUM(CASE Company WHEN 'MyCompany' THEN 0 ELSE DATEDIFF(yy, StartDate, EndDate) END) as YearsSomewhereElse
FROM dbo.EmpWorkHistory AS e
GROUP BY EmpNo
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.