MySQL Fill in the missing dates between two dates for a given status.

Question

I have a project data set. Projects change status from beginning to end, and the date the status changes is recorded in the table (the table is called "events" - not my choice). It will look like this (simplified):

Date Status 2015-06-01 Start 2015-06-03 Stage 2 2015-06-07 Stage 3 

In any given date range (which will be determined dynamically) I want to see which projects have status. However, using BETWEEN or another data query will only delete projects whose status has changed over this period, and not those that are still in a certain state.

Currently, I have created a very complex solution in Excel that copies rows to new rows between status change dates, for example:

 Date Status 2015-06-01 Project start 2015-06-02 Project start (copied) 2015-06-03 Stage 2 2015-06-04 Stage 2 (copied) 2015-06-05 Stage 2 (copied) 2015-06-06 Stage 2 (copied) 2015-06-07 Stage 3 

This solution allows me to request status for a project, say, 2015-06-06 and see that it is still in stage 2.

Is there a way to use mySql to pull the same data, but as output to the request? I heard some suggest using a calendar table, but I'm not sure how this will work. I also saw someone recommending Cross Join, but again, I couldn’t understand from the description how this would work.

Thanks in advance for your help!

Answer 1

plan

• Create a calendar table by cross-connecting numbers and date_add for the calendar period.
• attach your data to the calendar source with date <= calendar date
• accept maximum date <= calendar date
• join the source data source for status

Customization

 drop table if exists calendar_t; CREATE TABLE calendar_t ( id integer primary key auto_increment not null, `date` date not null, day varchar(9) not null, month varchar(13) not null, `year` integer not null ); drop view if exists digits_v; create view digits_v as select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 ; insert into calendar_t ( `date`, day, month, `year` ) select date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day) as `date`, dayname(date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)) as day, monthname(date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)) as month, year(date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)) as `year` from digits_v a2 cross join digits_v a1 cross join digits_v a0 order by date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day) ; drop table if exists example; create table example ( `date` date not null, status varchar(23) not null ); insert into example ( `date`, status ) values ( '2015-06-01', 'Start' ), ( '2015-06-03', 'Stage 2' ), ( '2015-06-07', 'Stage 3' ) ; 

request

 select cal_date, mdate, ex2.status from ( select cal_date, max(ex_date) as mdate from ( select cal.`date` as cal_date, ex.`date` as ex_date from calendar_t cal inner join example ex on ex.`date` <= cal.`date` ) maxs group by cal_date ) m2 inner join example ex2 on m2.mdate = ex2.`date` -- pick a reasonable end date for filtering.. where cal_date <= date('2015-06-15') order by cal_date ; 

Output

 +------------------------+------------------------+---------+ | cal_date | mdate | status | +------------------------+------------------------+---------+ | June, 01 2015 00:00:00 | June, 01 2015 00:00:00 | Start | | June, 02 2015 00:00:00 | June, 01 2015 00:00:00 | Start | | June, 03 2015 00:00:00 | June, 03 2015 00:00:00 | Stage 2 | | June, 04 2015 00:00:00 | June, 03 2015 00:00:00 | Stage 2 | | June, 05 2015 00:00:00 | June, 03 2015 00:00:00 | Stage 2 | | June, 06 2015 00:00:00 | June, 03 2015 00:00:00 | Stage 2 | | June, 07 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 | | June, 08 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 | | June, 09 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 | | June, 10 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 | | June, 11 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 | | June, 12 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 | | June, 13 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 | | June, 14 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 | | June, 15 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 | +------------------------+------------------------+---------+ 

sqlfiddle

---

link

• Mysql SO sequence
• sql cross join

Answer 2

You do not need to create a table with all dates. You can modify the table to give a start and end date for each status and use the operator.

or using existing data.

using @datequery as the date on which you want to know the status.

 Select top 1 Status from Events where Date <= @datequery and Date order by Date desc 

returns the most recent status change until the date you request.

 @datequery = 2015-06-06 Status Stage 2