MySQL select query to display incremental datetime and default value to some column

Question

I have a table called bug_history_table as given below

------------------------------------------------------------------------
| bug_id | date_modified       | field_changed | old_value | new_value |
------------------------------------------------------------------------
|  14415 | 2015-11-03 13:52:32 | status        | unconfirm | open      |
|  14415 | 2015-11-04 10:50:58 | status        | open      | resolved  |
|  14415 | 2015-11-10 11:27:41 | status        | resolved  | verified  |
|  14415 | 2015-11-14 11:27:41 | status        | verified  | closed    |
------------------------------------------------------------------------

The above table shows the bug history of status changed from open to closed. I want to show the results between two dates when the bug was idle i.e. status remain unchanged until its modified.

Expected Output -

--------------------------------------
| bug_id | date_modified | new_value |
--------------------------------------
|  14415 | 2015-11-03    | open      |
|  14415 | 2015-11-04    | resolved  |
|  14415 | 2015-11-05    | resolved  | <---
|  14415 | 2015-11-06    | resolved  | <---
|  14415 | 2015-11-07    | resolved  | <---  Need these extra rows.
|  14415 | 2015-11-08    | resolved  | <---
|  14415 | 2015-11-09    | resolved  | <---
|  14415 | 2015-11-10    | verified  |
|  14415 | 2015-11-11    | verified  | <---
|  14415 | 2015-11-12    | verified  | <---  Need these extra rows.
|  14415 | 2015-11-13    | verified  | <---
|  14415 | 2015-11-14    | closed    |
--------------------------------------

The entries are inserted only when bug status is modified. Now lets take example as below -

If bug is "Opened" on 2015-11-03 and then no user has updated its status till 2015-11-06 that means bug remained idle for 3 days. So I want to display that bug was in "Opened" state on 2015-11-03, 2015-11-04, 2015-11-05 these days.

Please check the description again.. I have updated the question — Abhishek Kulkarni, Nov 19 '15 at 07:08
Which programming language are you using? I'd be solving this using PHP and checking in a foreach-loop if the last `new_value` is different from the current `new_value` and decide to display additional dates with the last `new_value` untill it changes. — Ben Fransen, Nov 19 '15 at 07:14
@Ben I know programming language is a very simple option. However, Execution Time is a constraint - hence I am looking for pure SQL query or stored procedure. — Abhishek Kulkarni, Nov 19 '15 at 07:17
@Strawberry OK.. Execution won't be a constraint, but now I am curious to know how it can be achieved using SQL query... — Abhishek Kulkarni, Nov 19 '15 at 09:00

score 1 · Answer 1 · answered Nov 19 '15 at 10:57

The easiest way to make this solution is using a procedure, make a date table that you need and left join with this table.

drop temporary table if exists tmp_dates;
create temporary table tmp_dates(_date timestamp);
set @tmp_date = start_date;
while @tmp_date <= end_date do

    insert into tmp_dates values (@tmp_date);
    set @tmp_date = @tmp_date+interval 1 day;

end while;

select a._date,b.bug_id,ifnull(b.new_value,'resolved',b.new_value) from tmp_dates a
left join  bug_history_table b on a._date = b.date_modified;

This is a good option however the status value if null then to be set to previous status. I will have to write that part. Thanks — Abhishek Kulkarni, Nov 20 '15 at 03:46

score -1 · Answer 2 · edited May 23 '17 at 12:23

-1

You have to create procedure that select start and end date of bug and then return list of dates. The following thread will help to create procedure. Create date list

OR

Read this thread to generate dates using recursive query.

Which ever you understand best.

edited May 23 '17 at 12:23

Community

1
1

answered Nov 19 '15 at 07:19

Muhammad Muazzam

2,810
6
33
62

Please read it carefully. There are N number of bugs whose status is changed N number of times. These dates should be created between each changed status. e.g. Dates for BugN whose status is OPEN from date d1 to dn then for status RESOLVED from date d1 to dm and so on and when we create these intermediate dates, we have to set previous status value to the newly created date until next status is updated. – Abhishek Kulkarni Nov 19 '15 at 08:59
Yes you can get that result by using above technique. – Muhammad Muazzam Nov 19 '15 at 09:21
I am not asking for the logic to get list of intermediate dates.. The required logic is to have selected between dates in successive rows associated with same bug id and then have previous status value to be assigned to the new date until the next status is changed... – Abhishek Kulkarni Nov 19 '15 at 10:33

MySQL select query to display incremental datetime and default value to some column

2 Answers2