Click here to Skip to main content
15,891,253 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi, for my table i am having the data as below

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


for a particular month i need all the Intimes and Outtimes of my emplorees

i will give Date='2013-01-01' as input and i want to get output in the following way

   01-01-2013                      02-01-2013
RF_Code  Min     OutTime       Min     OutTime
104   09:00:00   18:12:00    09:02:00  19:05:00
105   09:30:00   18:30:00
Posted
Updated 14-May-13 2:53am
v3
Comments
Sudhakar Shinde 14-May-13 6:19am    
You need to transform rows to columns. Refer to (http://stackoverflow.com/questions/14214433/mysql-convert-rows-to-columns) to understand how it can be done in mysql.
ali from hyd 15-May-13 0:47am    
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


required output is:

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
Sudhakar Shinde 15-May-13 1:32am    
Have you referred to the link given by me? You just need to write a query and no need to write a procedure.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900