2

I have this table with 16 rows only:

http://www.jassimrahma.com/temp/attendence_table.png

and I am using below code now to split the time of attendance into columns and getting this result:

http://www.jassimrahma.com/temp/attendence_result.png

but I am not happy with it! for example, 7th July, there is only one fingerprint so it should be only F1 but it's repeating it in F1, F3 and F4.

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 1), ',', -1) AS F1,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 2), ',', -1) ,NULL) AS F2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 4), ',', -1) AS F4
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

How can I fix this please?

Thanks, Jassim

2

1 Answer 1

0

SUBSTRING_INDEX will repeat the value if the value doesnt not end with the respective delimiter,so just concat a comma at the end of each string

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 1), ',', -1) AS F1_time,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 2), ',', -1), NULL) AS F2_time,
-- SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3_time,
IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 3), ',', -1), NULL) AS F3_time,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 4), ',', -1) AS F4_time
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

FIDDLE

1
  • One more question..... Why for example 17 Sep shows the time 21:24:51 then 21:24:47 then 21:24:42 where is should be ASC order so 21:24:42 under F1, 21:24:47 under F2 and 21:24:51 under F3 Oct 27, 2015 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.