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 |
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}' ),
...