How to find the number of employees in a calender year?

Question

Basically this question was asked in an interview and I couldn't answer.

The tables employee is as follows:

  id |  name | start_date | end_date

    1      A     2015-01-09   2018-03-23
    2      B     2012-07-21   2019-01-22
    3      C     2013-04-07    null
              -
          -
          -

Now I have to find the id,name of all employees who worked during the year 2017.

Can you please help me.

This is a basic SQL query, what was it that you couldn't figure out? — Alexandre Elshobokshy, Feb 18 '19 at 14:49
@IslamElshobokshy It's actually not so basic, if you don't know the overlapping range problem. But yet, it would have been nice to see an attempt by the OP. — Tim Biegeleisen, Feb 18 '19 at 14:54
@TimBiegeleisen I disagree, the range problem is a simple algorithmic problem. Even if you're unfamiliar with SQL in general you could even guess — Alexandre Elshobokshy, Feb 18 '19 at 14:56

cn007b · Answer 1 · 2019-02-18T14:51:40.090

2

You may try something like this:

SELECT id FROM employee
WHERE YEAR(start_date) <= 2017 AND (end_date IS NULL OR YEAR(end_date) >= 2017)

hope this query doesn't require explanation, because it's pretty clear)

edited Feb 18 '19 at 14:51

answered Feb 18 '19 at 14:44

cn007b

16,596
7
59
74

2

This doesn't make sense to me. If the year of the start and end date were both in 2017, then that record should be a match, but your condition `YEAR(start_date) < 2017` would always rule out such a record. – Tim Biegeleisen Feb 18 '19 at 14:49
This would exclude any employee who has joined in 2017. – Darshan Mehta Feb 18 '19 at 14:50
@TimBiegeleisen exactly! – Darshan Mehta Feb 18 '19 at 14:50
@TimBiegeleisen Yeah... ty! Updated answer. – cn007b Feb 18 '19 at 14:54

Tim Biegeleisen · Answer 2 · 2019-02-18T14:50:54.740

1

This is just the overlapping range problem, which can be handled by this query:

SELECT id, name
FROM employees
WHERE
    ('2017-01-01' <= end_date OR end_date IS NULL) AND
    '2017-12-31' >= start_date;

edited Feb 18 '19 at 14:50

answered Feb 18 '19 at 14:44

Tim Biegeleisen

502,043
27
286
360

Not sure why we would need to check for the `end_date` though. – Darshan Mehta Feb 18 '19 at 14:52
@DarshanMehta Have a look at the comment under your answer. – Tim Biegeleisen Feb 18 '19 at 14:53
Yep, corrected it now, and deleted as it became the copy of your answer :) – Darshan Mehta Feb 18 '19 at 14:54
Looks correct but just curious as to the reasoning behind having the criteria before the field names? i.e. why '2017-01-01' <= end_date as opposed to end_date >= '2017-01-01' . Both are the same but it just looks strange, wondering if there is a reason for it? – ChrisCarroll Feb 18 '19 at 15:03
@ChrisCarroll I based my answer off [this accepted answer](https://stackoverflow.com/questions/2545947/check-overlap-of-date-ranges-in-mysql) and did not bother to rearrange things. You may do that if you want. – Tim Biegeleisen Feb 18 '19 at 15:12
@TimBiegeleisen that's fine, as I said I knew it wasn't any different but I just found it strange to look at, was afraid I was missing something. thanks – ChrisCarroll Feb 18 '19 at 15:36

score 0 · Answer 3 · answered Feb 18 '19 at 14:43

0

I think this will do,

select * from employee where start_date >= '2017-01-01' and end_date <= '2017-12-31'

answered Feb 18 '19 at 14:43

Santosh

874
11
21

How to find the number of employees in a calender year?

3 Answers3