By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE students (
sid INTEGER NOT NULL
, name varchar(20)
, lastname varchar(20)
, active varchar(3)
);
INSERT INTO students values (6020, 'John', 'Doe','Yes');
CREATE TABLE attendence(
`did` INTEGER NOT NULL
, `sid` INTEGER NOT NULL
, `arrived` TIMESTAMP NOT NULL
, `departed` TIMESTAMP NOT NULL
);
INSERT INTO `attendence` (`did`, `sid`, `arrived`, `departed`)
VALUES
(16655, 6020, '2023-09-08 09:25:00', '2023-09-08 14:54:00')
, (16673, 6020, '2023-09-11 09:36:00', '2023-09-11 16:00:00')
, (16694, 6020, '2023-09-12 09:27:00', '2023-09-12 16:17:00')
, (16713, 6020, '2023-09-13 09:21:00', '2023-09-13 15:35:00')
, (16735, 6020, '2023-09-14 09:52:00', '2023-09-14 15:00:00')
, (16754, 6020, '2023-09-15 09:19:00', '2023-09-15 15:20:00')
, (16783, 6020, '2023-09-19 09:27:00', '2023-09-19 16:23:00')
, (16799, 6020, '2023-09-20 09:22:00', '2023-09-20 15:58:00')
, (16816, 6020, '2023-09-21 09:14:00', '2023-09-21 14:54:00')
, (16842, 6020, '2023-09-22 09:24:00', '2023-09-22 15:58:00')
, (16859, 6020, '2023-09-25 09:24:00', '2023-09-25 14:59:00')
, (16884, 6020, '2023-09-26 09:19:00', '2023-09-26 16:16:00')
, (16928, 6020, '2023-09-28 09:30:00', '2023-09-28 14:51:00')
, (16948, 6020, '2023-09-29 09:33:00', '2023-09-29 15:15:00')
, (16948, 6020, '2023-09-29 09:33:00', '2023-09-29 15:14:59');
Records: 15 Duplicates: 0 Warnings: 0
select
d.*
, case when hhmm > 0 then round(hhmm / 30) else 0 end post_1500_units
, case when hhmm > 0 then hhmm % 30 end mm_remaining
from (
select
`arrived`
, `departed`
, extract(HOUR_MINUTE FROM a.departed) - 1500 hhmm
from attendence a
) d
arrived | departed | hhmm | post_1500_units | mm_remaining |
---|---|---|---|---|
2023-09-08 09:25:00 | 2023-09-08 14:54:00 | -46 | 0 | null |
2023-09-11 09:36:00 | 2023-09-11 16:00:00 | 100 | 3 | 10 |
2023-09-12 09:27:00 | 2023-09-12 16:17:00 | 117 | 4 | 27 |
2023-09-13 09:21:00 | 2023-09-13 15:35:00 | 35 | 1 | 5 |
2023-09-14 09:52:00 | 2023-09-14 15:00:00 | 0 | 0 | null |
2023-09-15 09:19:00 | 2023-09-15 15:20:00 | 20 | 1 | 20 |
2023-09-19 09:27:00 | 2023-09-19 16:23:00 | 123 | 4 | 3 |
2023-09-20 09:22:00 | 2023-09-20 15:58:00 | 58 | 2 | 28 |
2023-09-21 09:14:00 | 2023-09-21 14:54:00 | -46 | 0 | null |
2023-09-22 09:24:00 | 2023-09-22 15:58:00 | 58 | 2 | 28 |
2023-09-25 09:24:00 | 2023-09-25 14:59:00 | -41 | 0 | null |
2023-09-26 09:19:00 | 2023-09-26 16:16:00 | 116 | 4 | 26 |
2023-09-28 09:30:00 | 2023-09-28 14:51:00 | -49 | 0 | null |
2023-09-29 09:33:00 | 2023-09-29 15:15:00 | 15 | 1 | 15 |
2023-09-29 09:33:00 | 2023-09-29 15:14:59 | 14 | 0 | 14 |
select * ,
ROUND( TIME_TO_SEC(TIMEDIFF(time(a.departed), '15:00:00')) /1800 )
from attendence a
did | sid | arrived | departed | ROUND( TIME_TO_SEC(TIMEDIFF(time(a.departed), '15:00:00')) /1800 ) |
---|---|---|---|---|
16655 | 6020 | 2023-09-08 09:25:00 | 2023-09-08 14:54:00 | 0 |
16673 | 6020 | 2023-09-11 09:36:00 | 2023-09-11 16:00:00 | 2 |
16694 | 6020 | 2023-09-12 09:27:00 | 2023-09-12 16:17:00 | 3 |
16713 | 6020 | 2023-09-13 09:21:00 | 2023-09-13 15:35:00 | 1 |
16735 | 6020 | 2023-09-14 09:52:00 | 2023-09-14 15:00:00 | 0 |
16754 | 6020 | 2023-09-15 09:19:00 | 2023-09-15 15:20:00 | 1 |
16783 | 6020 | 2023-09-19 09:27:00 | 2023-09-19 16:23:00 | 3 |
16799 | 6020 | 2023-09-20 09:22:00 | 2023-09-20 15:58:00 | 2 |
16816 | 6020 | 2023-09-21 09:14:00 | 2023-09-21 14:54:00 | 0 |
16842 | 6020 | 2023-09-22 09:24:00 | 2023-09-22 15:58:00 | 2 |
16859 | 6020 | 2023-09-25 09:24:00 | 2023-09-25 14:59:00 | 0 |
16884 | 6020 | 2023-09-26 09:19:00 | 2023-09-26 16:16:00 | 3 |
16928 | 6020 | 2023-09-28 09:30:00 | 2023-09-28 14:51:00 | 0 |
16948 | 6020 | 2023-09-29 09:33:00 | 2023-09-29 15:15:00 | 1 |
16948 | 6020 | 2023-09-29 09:33:00 | 2023-09-29 15:14:59 | 0 |
SELECT
s.sid,
CONCAT(s.name, ' ', s.lastname) AS FullName,
COUNT(*) AS AfterschoolCount,
GROUP_CONCAT(DATE_FORMAT(a.departed, ' %d|%H:%i') ORDER BY a.departed ASC) AS Dates,
SUM(
CASE
WHEN a.departed IS NOT NULL THEN
CASE
WHEN extract(HOUR_MINUTE FROM a.departed) - 1500 > 0 THEN round((extract(HOUR_MINUTE FROM a.departed) - 1500) / 30)
ELSE 0
END
ELSE 0
END
) AS HalfHours,
SUM(
CASE
WHEN a.departed IS NOT NULL THEN
CASE
WHEN MINUTE(a.departed) % 30 >= 16 THEN FLOOR((TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) + 1799) / 1800) * 500
WHEN MINUTE(a.departed) % 30 <= 15 THEN CEIL((TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) - 899) / 1800) * 500
ELSE FLOOR((TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) + 899) / 1800) * 500
END
ELSE 0
END
) AS Amount
FROM
students s
JOIN
attendence a ON s.sid = a.sid
WHERE
TIME(a.departed) > '15:00:00' AND
MONTH(a.departed) = 9 AND
YEAR(a.arrived) = 2023 AND
s.active = 'Yes'
GROUP BY
sid | FullName | AfterschoolCount | Dates | HalfHours | Amount |
---|---|---|---|---|---|
6020 | John Doe | 10 | 11|16:00, 12|16:17, 13|15:35, 15|15:20, 19|16:23, 20|15:58, 22|15:58, 26|16:16, 29|15:14, 29|15:15 | 22 | null |