Get dates between two dates

Question

I have two tables like this:

Table1

emp_leave_summary(id,emp_id,leave_from_date,leave_to_date,leave_type)

Table2

emp_leave_daywise(id,emp_id,leave_date,leave_type)

I would want to select emp_id, leave_type from Table1 and insert into Table2.

for example: In table1 I have this

id,emp_id,leave_from_date,leave_to_date,leave_type
 1, 12345,2017-07-01     ,2017-07-03   ,Sick Leave

In table 2, I want to have this

id,emp_id,leave_date,leave_type
 1,12345,2017-07-01,Sick Leave
 2,12345,2017-07-02,Sick Leave
 3,12345,2017-07-03,Sick Leave

table structure with sample data

    CREATE TABLE `emp_leave_summary` (
  `id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `leave_from_date` date NOT NULL,
  `leave_to_date` date NOT NULL,
  `leave_type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `emp_leave_summary` (`id`, `emp_id`, `leave_from_date`, `leave_to_date`, `leave_type`) VALUES
(1, 123, '2017-02-01', '2017-02-15', 'Earned Vacation Leave'),
(2, 123, '2017-07-12', '2017-07-26', 'Earned Vacation Leave'),
(3, 456, '2017-03-20', '2017-04-20', 'Earned Vacation Leave'),
(4, 789, '2017-01-15', '2017-02-23', 'Earned Vacation Leave'),
(5, 789, '2017-02-26', '2017-02-27', 'Sick Leave');

CREATE TABLE `emp_leave_daywise` (
  `id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `leave_date` date NOT NULL,
  `leave_type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `emp_leave_daywise`
ADD PRIMARY KEY (`id`),
ADD KEY `emp_id` (`emp_id`),
ADD KEY `leave_date` (`leave_date`),
ADD KEY `leave_type` (`leave_type`);


ALTER TABLE `emp_leave_summary`
ADD PRIMARY KEY (`id`),
ADD KEY `emp_id` (`emp_id`),
ADD KEY `leave_type` (`leave_type`),
ADD KEY `leave_from_date` (`leave_from_date`),
ADD KEY `leave_to_date` (`leave_to_date`);

i believe using a procedure like this can help https://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates, but im new to sql need help — davidb, Aug 03 '17 at 17:19
@strawberry, i couldn't find any solutions for this from the relevant questions. — davidb, Aug 03 '17 at 17:33
i think you should make a `proceduce` and use `INTERVAL 1 DAY` to increase the date — Felix, Aug 04 '17 at 02:06
Not exactly the same but pretty close duplicate: https://stackoverflow.com/questions/22329722/sql-server-displaying-missing-dates — DrCopyPaste, Aug 04 '17 at 08:04
@Keith John Hutchison, i have updated my question with table structure and sample data — davidb, Aug 04 '17 at 08:48
This is kind of thing is normally handled in application code. — Strawberry, Aug 04 '17 at 08:55

score 2 · Answer 1 · edited Aug 04 '17 at 10:53

Try:

    select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'

-for date ranges up to nearly 300 years in the future.

from How to get list of dates between two dates in mysql select query

thanks for this, much appreciate,but i would want to have other columns as well in the output.like this `id,emp_id,leave_date,leave_type` — davidb, Aug 04 '17 at 08:52

score 0 · Answer 2 · answered Aug 04 '17 at 09:36

I have managed to get the preferred output using the below solution

step1

create a calendar table and insert the dates( all possible required dates) something like this

CREATE TABLE `db_calender` (
        `c_date` date NOT NULL);

then insert dates into the calendar table, to insert easily i used this procedure answered in this How to populate a table with a range of dates?, thanks for Mr.Leniel Macaferi.

    DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO tablename (_date) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;

| DELIMITER ; CALL filldates('2017-02-01','2017-07-31');

then i tried below this query to get the preferred output

    SELECT
     els.*, c.c_date
FROM
    emp_leave_summary els
     JOIN db_calender c ON c.c_date BETWEEN els.leave_from_date AND els.leave_to_date 
ORDER BY
     els.id,c.c_date

i thank everyone for their contribution and support.

Keith John Hutchison · Accepted Answer · 2017-08-04T22:45:39.587

Thanks for your schema. It makes it easy to work with your question. I changed your schema a little to make use of auto_increment

CREATE TABLE `emp_leave_summary` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_id` int(11) NOT NULL,
  `leave_from_date` date NOT NULL,
  `leave_to_date` date NOT NULL,
  `leave_type` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `emp_leave_summary` (`emp_id`, `leave_from_date`, `leave_to_date`, `leave_type`) VALUES
( 123, '2017-02-01', '2017-02-15', 'Earned Vacation Leave'),
( 123, '2017-07-12', '2017-07-26', 'Earned Vacation Leave'),
( 456, '2017-03-20', '2017-04-20', 'Earned Vacation Leave'),
( 789, '2017-01-15', '2017-02-23', 'Earned Vacation Leave'),
( 789, '2017-02-26', '2017-02-27', 'Sick Leave');

CREATE TABLE `emp_leave_daywise` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_id` int(11) NOT NULL,
  `leave_date` date NOT NULL,
  `leave_type` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here I added a unique constraint on the emp_leave_daywise table as a primary key on a id integer does not ensure records are not duplicated.

ALTER TABLE `emp_leave_daywise`
ADD UNIQUE KEY `emp_leave_daywise_unique_key` (`emp_id`,`leave_date`,`leave_type`),
ADD KEY `emp_id` (`emp_id`),
ADD KEY `leave_date` (`leave_date`),
ADD KEY `leave_type` (`leave_type`);

The unique key for emp_leave_summary needs some thought. For example ... do you allow summaries covering overlapping date ranges? ...

ALTER TABLE `emp_leave_summary`
ADD UNIQUE KEY `emp_leave_summary_unique_key` (`emp_id`,`leave_from_date`),
ADD KEY `emp_id` (`emp_id`),
ADD KEY `leave_type` (`leave_type`),
ADD KEY `leave_from_date` (`leave_from_date`),
ADD KEY `leave_to_date` (`leave_to_date`);

and now for the data extraction using a left join on existing data.

/*
    insert any missing records using a left join on existing records
    */
insert into emp_leave_daywise ( emp_id, leave_date, leave_type )
select `new`.* from 
( 
    select summary.emp_id, dates.date_ leave_date, summary.leave_type 
    from emp_leave_summary summary
    inner join (
    /* 
        get dates to match against
        https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query
        */
        select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) date_ from
        ( select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
        ( select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
        ( select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
        ( select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
        ( select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) dates
 on dates.date_ >= summary.leave_from_date 
 and dates.date_ <= summary.leave_to_date
 ) `new`
 left join emp_leave_daywise old
 on `new`.emp_id = old.emp_id
 and `new`.leave_date = old.leave_date 
 and `new`.leave_type = old.leave_type 
 where old.id is null ;

 select * from emp_leave_daywise order by leave_date, emp_id;

returns 104 rows based on the data given

 id emp_id  leave_date  leave_type
 1  789 2017-01-15  Earned Vacation Leave
 2  789 2017-01-16  Earned Vacation Leave
 3  789 2017-01-17  Earned Vacation Leave
 4  789 2017-01-18  Earned Vacation Leave
 5  789 2017-01-19  Earned Vacation Leave
 6  789 2017-01-20  Earned Vacation Leave
 ...
 102    123 2017-07-24  Earned Vacation Leave
 103    123 2017-07-25  Earned Vacation Leave
 104    123 2017-07-26  Earned Vacation Leave

The date range creation SQL is from here How to get list of dates between two dates in mysql select query

thanks a lot it works great. and i like the `ADD UNIQUE KEY emp_leave_daywise_unique_key (emp_id,leave_date,leave_type),` which will ensure that there is not duplicate entries. — davidb, Aug 05 '17 at 11:17

Get dates between two dates

3 Answers3

Linked