I'm working on for presence control. The check-in and check-out are 2 difference records in a table
It is possible that somebody does not check-out for several days, but should count as present for the particular day
person ID - CheckIn - CheckOut
3842 12/17/2022 09:030 -- never check out (should count every day after 12/17.2022 as well)
3843 12/17/2022 08:00 -- 12/17/2022 09:30. (same day below)
3843 12/17/2022 11:00 -- 12/17/2022 13:30. (same day above)
3841 12/17/2022 08:00 -- 12/17/2022 17:45. (simple same day)
3844 12/17/2022 22:00 -- 12/18/2022 6:40. (crosses midnight - should count 12/17 and 12/18)
I would expect following result
12/15 1 peron
12/17 4 persons
12/18 2 persons
I want to see how many have been present on day X
I am a little bit stuck how to do it with 2 different records (check-in and check-out) or only 1 recrods available ()check-in
CREATE TABLE `my_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`person_id` int(11) DEFAULT NULL,
`action` varchar(24) DEFAULT NULL,
`when_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Some insert statements
INSERT INTO `my_history` ( `person_id`, `action`, `when_created`)
VALUES
( 3842, 'checked_in', '2022-12-15 08:00:00'),
( 3842, 'checked_out', '2022-12-15 09:30:00'),
( 3842, 'checked_in', '2022-12-17 09:30:00'),
( 3843, 'checked_in', '2022-12-17 08:00:00'),
( 3843, 'checked_out', '2022-12-17 09:30:00'),
( 3843, 'checked_in', '2022-12-17 11:00:00'),
( 3843, 'checked_out', '2022-12-17 13:30:00'),
( 3841, 'checked_in', '2022-12-17 08:00:00'),
( 3841, 'checked_out', '2022-12-17 17:42:00'),
( 3844, 'checked_in', '2022-12-17 22:00:00'),
( 3844, 'checked_out', '2022-12-18 06:40:00');
CREATE TABLE person (
id INT(11)
)
INSERT INTO
person
VALUES
(3841),
(3842),
(3843),
(3844)
Old answer removed as requirement changed. In rush, no time to explain it.
SELECT
param.check_date,
COUNT(DISTINCT p.id)
FROM
(
SELECT timestamp '2022-12-15 00:00' AS check_date
UNION ALL
SELECT timestamp '2022-12-17 00:00' AS check_date
UNION ALL
SELECT timestamp '2022-12-18 00:00' AS check_date
)
AS param
CROSS JOIN
person AS p
INNER JOIN
my_history AS h
ON
h.person_id = p.id
AND
h.when_created
>=
COALESCE(
(
SELECT when_created
FROM my_history
WHERE person_id = p.id
AND when_created <= check_date
ORDER BY when_created DESC
LIMIT 1
),
check_date
)
AND
h.when_created < check_date + INTERVAL 1 DAY
AND
h.action = 'checked_in'
GROUP BY
param.check_date