[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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!
0
jackjohnson44
Asked:
jackjohnson44
  • 2
2 Solutions
 
Ryan McCauleyData 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

Open in new window

0
 
Ryan McCauleyData 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

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now