hi,
for my application i have the following data
RF_Code Date Time
104 2013-01-01 09:00:00
104 2013-01-01 18:12:00
104 2013-01-02 09:02:00
104 2013-01-02 19:05:00
105 2013-01-01 09:30:00
105 2013-01-01 18:30:00
i want to get the output as below
RF_Code 2013-01-01 2013-01-02
104 09:00:00,18:12:00 09:02:00,19:05:00
105 09:30:00,18:30:00 null,null
i written the following procedure and i am getting only one type of time only ie., By selecting min time or max time.
i want to concatenate both min time and max time with comma seperated
use ecampus;
DELIMITER $$
DROP PROCEDURE IF EXISTS `GetTimeByDate` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTimeByDate`(in sdate date)
BEGIN
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select RF_Code,";
declare curs cursor for select Date from ecampus.log where (month(Date)=month(sdate) AND YEAR(Date)=YEAR(sdate)) group by Date;
declare continue handler for not found set finish = 1;
open curs;
my_loop:loop
fetch curs into cdate;
if finish = 1 then
leave my_loop;
end if;
set str = concat(str, "min(case when Date = '",cdate,"' then Time else null end) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from ecampus.log
group by RF_Code");
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
END $$
DELIMITER ;
i am getting output as below:
RF_Code 2013-01-01 2013-01-02
104 09:00:00 09:02:00
105 09:30:00 null