Select changes in the years

[acs_one]

I have a table with list of employees.


For each employee there are name, surname, start_date, end_date, department.
Each employee has one record for each year, then if one has worked for n years in the company in the table there are n rows.

Now, I have to select only the employees that in the several years have changed their department.

How can I do?

[RuralGuy]

What on earth are in all of the records? If they are all as you have described for that first record then you may wish to revisit the design. You may just be starting to experience the problems with the design. There *will* be others.

[acs_one]

I can't revisit the design because I take this datas from an export given by a program not managed by me and where I haven't an access to DB.
This program gives me a unique big table where I have the structure like i've described on the first post.

The table has one record per year for each employee (then if an employee doesn't change department in the years i've have the same record in the severals years, where the unique difference between the rows is in the start_date and in the end_date.

Instead, if one employee changes his department in the table i will have 2 records for the year for this employee:

• in the first record i have the end_date equals to the last day of works in the first department;
• in the second record i have the start_date equals to the first day of works in the new department

[RuralGuy]

I can't revisit the design because I take this datas from an export given by a program not managed by me and where I haven't an access to DB.

Understood.
So you are looking for records where the Department is not consistant for all of the records for that employee, right?

[acs_one]

Nearly.
I'm searching for employees that in the last two years have changed department.

[RuralGuy]

So there will *only* be possibly two records to deal with right? Does the StartDate of EndDate change?

[acs_one]

I'm not sure to have understood what did you mean.

I'm dealing with datas of three years, then i've at least three rows for each employee (unless someone is ceased before 2010 or recruited after 2008).

About the start_date and the end_date, they can be the same if someone doesn't change department during the year (then the start_date is for example 2008-01-01 and the end_date 2008-12-31) but this doesn't mean nothing because this employee may have changed the department on 2009-01-01.

I hope that I explained myself well.

[RuralGuy]

You did. I'll give it some thought.

[acs_one]

Thanks a lot!