Mysql – building structure for employee office relation

MySQL

I have a table defined like this 

  empid   fromdate        todate       office_ID
    1     2012-03-01      2014-04-2      1
    1     2014-04-03      ----           2
    2     2012-03-01      2014-04-2      2
    3     2012-03-01      2014-04-2      1

If the employee is currently working at some office then todate will be not known hence it is null. This logic can be changed if you suggest something better.

From this table I want to fetch an employee's location(s) for a given month. For example, for May 2005 where was employee 1? For that I have designed this query

select * 
from tbl_emp_office 
where (year(From_date)*100 + month(From_date)) < '201505'

This structure is not giving the exact solution to my problem. Can you suggest more suitable structure for this?

EDIT
fromdate has data type Date.

Best Answer

What "datatype" is From_date? Assuming it is DATE or DATETIME or TIMESTAMP, then this will find records before May, 2015:

select * from tbl_emp_office where From_date < '2015-05-01';

This will find records for only May, 2015:

select * from tbl_emp_office 
    where From_date >= '2015-05-01'
      and From_date  < '2015-05-01' + INTERVAL 1 MONTH;

If you are using VARCHAR, well don't.

Edit to answer "Where was emp#1 working during May, 2015?"

SELECT  office_id
    FROM  tbl_emp_office
    WHERE  empid = 1
      AND  From_date < '2015-05-01' + INTERVAL 1 MONTH -- Started before/during May
      AND  ( To_date IS NULL          -- Still working there
         OR  To_date >= '2015-05-01'  -- Left during/after May
           );

Note: If he worked in multiple offices during May, this will produce multiple rows.

Related Question