Split column datetime values with into multiple columns

Issue

With a MySQL query, how can I take a table like in Example A:
Example A

ID time log_type
1111 2021-06-01 07:13:42.000000 IN
1111 2021-06-01 17:13:42.000000 OUT
1111 2021-06-03 07:00:42.000000 IN
1111 2021-06-03 17:00:42.000000 OUT
2222 2021-06-01 07:13:42.000000 IN
2222 2021-06-01 17:13:42.000000 OUT
2222 2021-06-03 07:00:42.000000 IN
2222 2021-06-03 17:00:42.000000 OUT

And create a view like Example B?
Example B

ID Date IN OUT
1111 2021-06-01 7:13 am 4:30 pm
1111 2021-06-02 NA NA
1111 2021-06-03 7:00 am 4:00 pm
2222 2021-06-01 7:13 am 4:30 pm
2222 2021-06-02 NA NA
2222 2021-06-03 7:00 am 4:00 pm

Solution

WITH RECURSIVE
cte1 AS ( SELECT MIN(DATE(`time`)) `date`, MAX(DATE(`time`)) maxtime
          FROM test
          UNION ALL
          SELECT `date` + INTERVAL 1 DAY, maxtime
          FROM cte1
          WHERE `date` < maxtime ),
cte2 AS ( SELECT DISTINCT id
          FROM test )
SELECT cte2.id, 
       cte1.`date`, 
       COALESCE(TIME(t1.`time`), 'N/A') `IN`,
       COALESCE(TIME(t2.`time`), 'N/A') `OUT`
FROM cte1
CROSS JOIN cte2
LEFT JOIN test t1 ON t1.id = cte2.id 
                 AND cte1.`date` = DATE(t1.`time`) 
                 AND t1.log_type = 'IN'
LEFT JOIN test t2 ON t2.id = cte2.id 
                 AND cte1.`date` = DATE(t2.`time`)
                 AND t2.log_type = 'OUT'
ORDER BY 1,2

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=51236ef22fcaaba3f2d9af9acf7f6c55


where do we put our variable, to return from_date to_date – Ashanti

WITH RECURSIVE
cte1 AS ( SELECT '{from_date}' `date`
          UNION ALL
          SELECT `date` + INTERVAL 1 DAY
          FROM cte1
          WHERE `date` < '{to_date}' ),
...

Answered By – Akina

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published