"SQL query for valid records within a specific date range"

Question

I am working on attendance control. Check-in and check-out are two different records in the table

It is possible that someone did not sign out for a few days, but it should be counted as attendance on the day

Personnel ID - Check-in time - Check-out time

3842 12/17/2022 09:030 -- Never checked out (every day after 12/17.2022 should be counted)

3843 12/17/2022 08:00 -- 12/17/2022 09:30. (The following is the same day)

3843 12/17/2022 11:00 -- 12/17/2022 13:30. (The above is the same day)

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. (Crossing midnight - 12/17 and 12/18 should be counted)

My desired result is

12/15 1 person
12/17 4 people
12/18 2 people

I want to know how many people are present on X day

I'm a little confused how to handle two different records (check-in and check-out) or only one record 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 (
  idINT(11)
)

INSERT INTO
  person
VALUES
  (3841),
  (3842),
  (3843),
  (3844)

Answer 1

Typically, to test this situation, you need to see if the date you are interested in is greater than the check-in date and less than the check-out date. For example

WHERE checkin <= '2022-12-14' and
      checkout >= '2022-12-14'

To solve the situation where the check-out date can be empty but still "OK", we just replace the empty value with a value that meets the condition.

SELECT *
FROM my_history
WHERE checkin <= '2022-12-14' and
      COALESCE(checkout,'2022-12-14') >= '2022-12-14'

Answer 2

Old answers have been deleted due to changes in requirements. In the rush, there was no time to explain.

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

https://dbfiddle.uk/RXx0x9xt