Split date range into indiviual records

Question

I have a table called staff_leave, and I want to split date range to days per row. is it possible. I am a newbie please take it easy .

like: there is a date range 01/01/2013 - 05/01/2013

and i want to split it to 5 days and store each day in each single row of another table.

CREATE TABLE IF NOT EXISTS `staff_leave` (
  `id_staff_leave` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id_staff` int(11) NOT NULL,
  `leave_type_id_leave_type` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `joining_date` date NOT NULL,
  `is_half_day` date DEFAULT NULL,
  `approved` int(11) DEFAULT NULL,
  `approved_date` date DEFAULT NULL,
  PRIMARY KEY (`id_staff_leave`),
  UNIQUE KEY `id_staff_leave_UNIQUE` (`id_staff_leave`),
  KEY `fk_staff_leave_staff1` (`staff_id_staff`),
  KEY `fk_staff_leave_leave_type1` (`leave_type_id_leave_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Answer 1

I am assuming the following:

1. This is a one time job you want to get done
2. You want another table, in almost identical format to the former, where each row in the original table is duplicated many times in the new table, as many times as there are days between start_date and end_date.

Here's a way to do so:

First, create a table with all possible dates (actually you can use an integer tally table as described above, but I will simplify for purpose of this demonstration):

CREATE TABLE all_dates(dt DATETIME PRIMARY KEY);

INSERT into the above table all dates possible in your system (e.g. 1970-01-01 and up to 2199-12-31, whatever). I mentioned my assumption is that this is a one time job, so this table can be later dropped.

Also create your new exploded table:

CREATE TABLE IF NOT EXISTS `staff_leave_exploded` (
  `id_exploded` int unsigned AUTO_INCREMENT
  `id_staff_leave` int(11) NOT NULL,
  `staff_id_staff` int(11) NOT NULL,
  `leave_type_id_leave_type` int(11) NOT NULL,
  `single_date` date NOT NULL,
  `joining_date` date NOT NULL,
  `is_half_day` date DEFAULT NULL,
  `approved` int(11) DEFAULT NULL,
  `approved_date` date DEFAULT NULL,
  PRIMARY KEY (`id_exploded`),
  KEY `id_staff_leave` (`id_staff_leave`),
  KEY `fk_staff_leave_staff1` (`staff_id_staff`),
  KEY `fk_staff_leave_leave_type1` (`leave_type_id_leave_type`)
) ENGINE=InnoDB;

And now

INSERT INTO staff_leave_exploded SELECT
  NULL, id_staff_leave, staff_id_staff, leave_type_id, 
  dt,
  joining_date, is_half_day, approved, approved_date
FROM 
  staff_leave JOIN all_dates ON (all_dates.dt BETWEEN start_date AND end_date)
;

Answer 2

This assumes you have a tally table tally with the numbers in a column id. If you don't have one, getchew you one.

N.B.: I have not tested this. I'll be happy to correct any syntax errors discovered, but I have a tough time reading the tiny type on the SSH client on my phone :-) Meanwhile, standard disclaimers of warranty are implied.

CREATE TABLE `other_table` (
    `id_staff_leave` int(11) NOT NULL,
    `id_staff_leave_day_num` int(11) NOT NULL,
    `leave_date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

delimiter //
CREATE PROCEDURE split_dates()
BEGIN

   DECLARE v_start DATE;
   DECLARE v_end DATE;
   DECLARE v_id INT;

   DECLARE cur_leave CURSOR FOR
   SELECT id_staff_leave, start_date, end_date
   FROM staff_leave;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

   OPEN cur_leave;

   REPEAT
   FETCH cur_leave INTO v_id, v_start, v_end;
       INSERT INTO other_table
       SELECT v_id, t.id, DATE_ADD(v_start, INTERVAL (t.id - 1) DAY)
       FROM tally t
       WHERE t.id BETWEEN 1 AND DATEDIFF(v_end, v_start) + 1;


   UNTIL flag
   END REPEAT;

   CLOSE cur_leave;
END

I have assumed that staff_leave.end_date represents the last day of leave rather than the first day back to work. I have seen it used both ways in the wild. If staff_leave.end_date is actually a day of work rather than a day on leave, you will want to remove the + 1 from the WHERE clause, as other_table is only storing actual days on leave, as I read your question..

I'm sure there is a better way to deal with this than using a cursor, but I couldn't think of a proper join off the top of my head. Assuming this is a one-time operation, it shouldn't be all that costly to use a cursor. You know, just this once. :)