0

I have a table in which information about employee attendance.

This is how the table looks like:

att_id scan_date scan_in_time scan_out_time
1 2023-10-21 2023-10-21 08:10:00 2023-10-21 16:20:00
2 2023-10-21 2023-10-21 07:36:00 0000-00-00 00:00:00
4 2023-10-21 2023-10-21 07:15:00 2023-10-21 16:43:00
1 2023-10-22 2023-10-22 08:25:50 2023-10-22 16:23:10

Is it possible to get output like below?

att_id 2023-10-21 2023-10-22
1 08:10:00 - 16:20:00 08:25:50 - 16:23:10
2 07:36:00 - NULL NULL
4 07:15:00 - 16:43:00 NULL

i'm using MariaDB 10.4 in production environments. thanks in advance!

FannyKaunang
  • 91
  • 1
  • 8
  • 1
    send me your email. I have the sql statement for your table buffen@t-online.de – Bernd Buffen Jul 03 '23 at 19:09
  • HERE is quer Answer: use Prepared Statement like this: SELECT CONCAT( "SELECT att_id, ",GROUP_CONCAT(CONCAT("max(if(scan_date = '", c.scan_date,"',CONCAT(COALESCE(TIME(scan_in_time),'NULL'),' - ',COALESCE(TIME(scan_out_time),'NULL')),null)) AS '", c.scan_date,"'" ) )," FROM ea GROUP BY att_id ORDER BY att_id") into @myq FROM ( SELECT DISTINCT scan_date FROM ea ORDER BY scan_date ) as c; -- only for Testing SELECT @myq; PREPARE stmt FROM @myq; EXECUTE stmt; DEALLOCATE PREPARE stmt; – Bernd Buffen Jul 03 '23 at 19:16
  • Note [EXECUTE IMMEDIATE CONCAT(...)](https://mariadb.com/kb/en/execute-immediate/) is a single statement version for MariaDB, since its not MySQL. – danblack Jul 03 '23 at 23:32
  • @BerndBuffen. thank you so much! your query was run as well – FannyKaunang Jul 04 '23 at 05:34

0 Answers0