Split date range into indiviual records 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 ;
I am assuming the following:


This is a one time job you want to get done
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)
;