Select records from next date
-- create table
CREATE TABLE `sc_attendance` (
`attendance_id` bigint(20) NOT NULL AUTO_INCREMENT,
`fk_employee_id` bigint(20) DEFAULT NULL,
`device_name` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
`attendance_date` datetime DEFAULT NULL,
`csn_no` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`in_time` datetime DEFAULT NULL,
`out_time` datetime DEFAULT NULL,
`created_on` datetime DEFAULT NULL,
`updated_on` datetime DEFAULT NULL,
`is_sync` tinyint(1) DEFAULT '0' COMMENT '0= not synced, 1 = synced',
`last_sync` datetime DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- sample data
insert into `sc_attendance` (`attendance_id`, `fk_employee_id`, `device_name`, `attendance_date`, `csn_no`, `in_time`, `out_time`, `created_on`, `updated_on`, `is_sync`, `last_sync`) values('1','1','abc','2015-04-01 00:00:00','12','2015-04-01 08:00:00',NULL,'2015-04-13 11:56:44','2015-04-13 11:56:50','0',NULL);
insert into `sc_attendance` (`attendance_id`, `fk_employee_id`, `device_name`, `attendance_date`, `csn_no`, `in_time`, `out_time`, `created_on`, `updated_on`, `is_sync`, `last_sync`) values('2','1','abc','2015-04-01 00:00:00','12',NULL,'2015-04-01 17:00:00','2015-04-13 11:56:44','2015-04-13 11:56:50','0',NULL);
insert into `sc_attendance` (`attendance_id`, `fk_employee_id`, `device_name`, `attendance_date`, `csn_no`, `in_time`, `out_time`, `created_on`, `updated_on`, `is_sync`, `last_sync`) values('3','1','abc','2015-04-02 00:00:00','12','2015-04-02 08:00:00',NULL,'2015-04-13 11:56:44','2015-04-13 11:56:50','0',NULL);
insert into `sc_attendance` (`attendance_id`, `fk_employee_id`, `device_name`, `attendance_date`, `csn_no`, `in_time`, `out_time`, `created_on`, `updated_on`, `is_sync`, `last_sync`) values('5','1','abc','2015-04-02 00:00:00','12',NULL,'2015-04-02 18:00:00','2015-04-13 11:56:44','2015-04-13 11:56:50','0',NULL);
insert into `sc_attendance` (`attendance_id`, `fk_employee_id`, `device_name`, `attendance_date`, `csn_no`, `in_time`, `out_time`, `created_on`, `updated_on`, `is_sync`, `last_sync`) values('6','2','abc','2015-04-03 00:00:00','12','2015-04-03 21:00:00',NULL,'2015-04-13 11:56:44','2015-04-13 11:56:50','0',NULL);
insert into `sc_attendance` (`attendance_id`, `fk_employee_id`, `device_name`, `attendance_date`, `csn_no`, `in_time`, `out_time`, `created_on`, `updated_on`, `is_sync`, `last_sync`) values('7','2','abc','2015-04-04 00:00:00','12',NULL,'2015-04-04 06:00:00','2015-04-13 11:56:44','2015-04-13 11:56:50','0',NULL);
insert into `sc_attendance` (`attendance_id`, `fk_employee_id`, `device_name`, `attendance_date`, `csn_no`, `in_time`, `out_time`, `created_on`, `updated_on`, `is_sync`, `last_sync`) values('8','2','kjh','2015-04-05 00:00:00',NULL,'2015-04-05 21:00:00',NULL,NULL,NULL,'0',NULL);
insert into `sc_attendance` (`attendance_id`, `fk_employee_id`, `device_name`, `attendance_date`, `csn_no`, `in_time`, `out_time`, `created_on`, `updated_on`, `is_sync`, `last_sync`) values('9','2','yhu','2015-04-06 00:00:00',NULL,NULL,'2015-04-06 05:00:00',NULL,NULL,'0',NULL);
I have to compute first in_time and Last out_time for employees. But as per shift.
e.g. Shift-1 08:00AM-6:00PM
shift-2 = 06:00PM-02:00AM.
problem is with sencond shift, as outtime exist in other date. I wrote following sql , but failed. Can you please tell how can I make it ?
SELECT
fk_employee_id
,date_format(attendance_date,'%m-%d-%Y') as AttendanceDate
,min(date_format(in_time,'%T')) as TimeIn
,case when min(date_format(in_time,'%T')) > '17:59:00' then (select max(date_format(out_time,'%T'))
from sc_attendance
where date_format(attendance_date,'%m-%d-%Y') = date_add(date_format(attendance_date,'%m-%d-%Y'),interval 1 day)
)
else max(date_format(out_time,'%T') )
end as TimeOut
FROM customer_5.sc_attendance
group by fk_employee_id,AttendanceDate;
Thanks