sql presence specific date valid from and valid to records-CodePudding

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)

CodePudding user response：

You could use a correlated sub-query to find the latest row (per person) before a set date, and check if that row is a check-in, as part of an EXISTS check.

SELECT
  COUNT(*)
FROM
  person
WHERE
  EXISTS (
    SELECT * 
      FROM (
             SELECT *
               FROM my_history
              WHERE person_id = person.id
                AND when_created < ('2022-12-14'   INTERVAL 1 DAY)
           ORDER BY when_created DESC
              LIMIT 1
           )
             AS latest_record
     WHERE action = 'checked_in'
  )

Alternatively, you could use ROW_NUMBER to find the latest row before a set date, per person, then check the action.

I suspect it's not as cost effective as EXISTS, but it's certainly cleaner.

WITH
  trimmed_sorted AS
(
  SELECT
    *, 
    ROW_NUMBER() OVER (
      PARTITION BY person_id
          ORDER BY when_created DESC
    )
      AS sort_id
  FROM
    my_history
  WHERE
    when_created < ('2022-12-14'   INTERVAL 1 DAY)
)
SELECT
  COUNT(*)
FROM
  trimmed_sorted
WHERE
      sort_id = 1
  AND action = 'checked_in'

Both Demos; https://dbfiddle.uk/wFX_WLY5

Ohhh, I slept through the first one, it can be simpified to...

SELECT
  COUNT(*)
FROM
  person
WHERE
  (
    SELECT action
      FROM my_history
     WHERE person_id = person.id
       AND when_created < ('2022-12-14'   INTERVAL 1 DAY)
  ORDER BY when_created DESC
     LIMIT 1
  )
  =
  'checked_in'

https://dbfiddle.uk/-Bl8WqQZ

CodePudding user response：

Normally to test for this case you would see if the date you were interested in was greater than the checkin date and less than the checkout date. eg

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

To solve the case where checkout can be null and still be "OK" we just replace null with a value that will satisfy the condition.

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