MySQL Forums
Forum List  »  Stored Procedures

Re: insert extra rows(s) in a SELECT statement
Posted by: irek kordirko
Date: February 16, 2012 04:39PM

Hi,

you can create a table with sequence of dates,
and fill it with a procedure,
like this:

create table mtab(
  date datetime,
  value varchar(100)
);

insert into mtab
select '2012-02-15 00:00:00' , 'a' union
select '2012-02-15 01:00:00' , 'b' union
select '2012-02-15 03:00:00' , 'd' union
select '2012-02-15 04:00:00' , 'e' 
;


create table dat_seq(
 dt datetime primary key 
);



delimiter $$
drop procedure if exists fill_sequence_table $$
create procedure fill_sequence_table( mindate datetime, maxdate datetime )
begin
  declare hours int;
  declare i int;
  truncate table dat_seq;
  set hours = timestampdiff(hour, mindate, maxdate);
  set i = 0;
  while hours >= 0 do
    insert into dat_seq values(  timestampadd( hour, i, mindate ));
    set hours = hours - 1;
    set i = i + 1;
  end while;
end;
$$

delimiter ;

call fill_sequence_table( ( select min( date ) from mtab ),
                          ( select max( date ) from mtab )
 );


then use this simple query:

select date,
       case when value is null then @lastvalue
            else (@lastvalue := value )
       end value
from (
    select ds.dt date, mt.value
    from dat_seq ds
    left join mtab mt
    on ds.dt = mt.date
    order by ds.dt
) x
cross join (
    select (@lastvalue:=value)
    from mtab
    order by date limit 1
) dummy
;


+---------------------+-------+
| date                | value |
+---------------------+-------+
| 2012-02-15 00:00:00 | a     |
| 2012-02-15 01:00:00 | b     |
| 2012-02-15 02:00:00 | b     |
| 2012-02-15 03:00:00 | d     |
| 2012-02-15 04:00:00 | e     |
+---------------------+-------+
5 rows in set (0.00 sec)


Options: ReplyQuote


Subject
Views
Written By
Posted
2200
February 15, 2012 10:43AM
Re: insert extra rows(s) in a SELECT statement
997
February 16, 2012 04:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.