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
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;
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) ;
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. 🙂