add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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