Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ;
share|improve this question
1  
Could you please clarify what do you mean by "split date range to day per row"? Best would be if you could provide an example data, and example of resulting data. –  Shlomi Noach Jan 16 '13 at 6:22
    
updated question, please have a look –  Scriptmafia Tk Jan 16 '13 at 7:28
    
I cannot get 'split it to 5 days and store each day in each single row of another table' from your update. Can you show output sample? –  neo Jan 16 '13 at 10:10
    
I have a question: date range 01/01/2013 - 05/01/2013. Since you mentioned 5 days, is that date format dd/mm/yyyy ? –  RolandoMySQLDBA Jan 16 '13 at 17:30
    
Just as a minor comment, the id_staff_leave_UNIQUE key is redundant since it's exactly the same as the PRIMARY KEY –  Shlomi Noach Jan 17 '13 at 8:58

2 Answers 2

up vote 1 down vote accepted

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)
;
share|improve this answer
    
And there's the join I couldn't conjure. Nice. –  mdoyle Jan 17 '13 at 14:36

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. :)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.