CODEWITHSUNDEEP
mysqlsql
John Guan
John Guan

Reputation: 744

MySQL split date range by month into rows

I have a employee list with start date and end date. Everytime they change manager, a new line is generated. I am trying to figure out what was their department for each month throughout the whole year.

Here's the data:

EMP_ID  startdate   enddate     staffgroup
494694  2012-05-24  2013-01-09  Service
494694  2013-01-09  2013-02-03  Service
494694  2013-02-03  2013-02-04  Service
494694  2013-02-04  2013-02-05  Service
494694  2013-02-05  2013-02-07  Service
494694  2013-02-07  2013-02-15  Service
494694  2013-02-15  2013-03-20  Service
494694  2013-03-20  2013-06-01  Service
494694  2013-06-01  2013-06-03  Manager
494694  2013-06-03  2013-07-01  Manager
494694  2013-07-01  2099-12-31  Manager

Expected results

EMP_ID  Month       staffgroup
494694  2013-01-01  Service
494694  2013-02-01  Service
494694  2013-03-01  Service
494694  2013-04-01  Service
494694  2013-05-01  Service
494694  2013-06-01  Manager
494694  2013-07-01  Manager

Upvotes: 2

Views: 2679

Answers (2)

mdahlman
mdahlman

Reputation: 9390

Create a DATE_HELPER table containing all the months you want:

the_date
2013-01-01
2013-02-01
...
2013-12-01

Then it looks like you want the last staffgroup that the person had before the start of each month.

         SELECT e.EMP_ID, max(e.startdate), dh.the_date
           FROM DATE_HELPER dh
LEFT OUTER JOIN RAW_EMP_DATA e on (e.startdate <= dh.the_date)
       GROUP BY EMP_ID, dh.the_date

Results:
EMP_ID  startdate   the_date
494694  2012-05-24  2013-01-01
494694  2013-01-09  2013-02-01
494694  2013-02-15  2013-03-01
494694  2013-03-20  2013-04-01
494694  2013-03-20  2013-05-01
494694  2013-06-01  2013-06-01
494694  2013-07-01  2013-07-01
494694  2013-07-01  2013-08-01
494694  2013-07-01  2013-09-01
494694  2013-07-01  2013-10-01

That gives you all of the relevant startdates you care about. So you join back to the original table to get the staffgroup:

    SELECT red.EMP_ID, X.the_date as Month, red.staffgroup
      FROM RAW_EMP_DATA red
INNER JOIN (
         SELECT e.EMP_ID, max(e.startdate), dh.the_date
           FROM DATE_HELPER dh
LEFT OUTER JOIN RAW_EMP_DATA e on (e.startdate <= dh.the_date)
       GROUP BY EMP_ID, dh.the_date
           ) X on (X.EMP_ID = red.EMP_ID and X.startdate = red.startdate)

Results:
EMP_ID  Month       staffgroup
494694  2013-01-01  Service
494694  2013-02-01  Service
494694  2013-03-01  Service
494694  2013-04-01  Service
494694  2013-05-01  Service
494694  2013-06-01  Manager
494694  2013-07-01  Manager
494694  2013-08-01  Manager
494694  2013-09-01  Manager
494694  2013-10-01  Manager

Upvotes: 1

S&#233;bastien
S&#233;bastien

Reputation: 12139

Something like:

SELECT EXTRACT( YEAR_MONTH FROM startdate ) 

FROM  `employee`

will give you '201310' for october 2013

Upvotes: 0

Related Questions