Question 21546 in Mysql Question posted in
The official MySQL documentation can be found here.

sql presence specific date valid from and valid to records – Mysql

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)
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you @MatBailie that is great.

    I additional converted it to MS SQL as well.

    MySql

    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

    MS SQL

    SELECT
  param.check_date,
  COUNT(DISTINCT p.id)
FROM
(
  SELECT CONVERT(DATETIME, '2022-12-15 00:00', 102) check_date
  UNION ALL
  SELECT CONVERT(DATETIME, '2022-12-17 00:00', 102) check_date
  UNION ALL
  SELECT CONVERT(DATETIME, '2022-12-18 00:00', 102) 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 TOP (1) when_created
            FROM my_history
           WHERE person_id = p.id
             AND when_created <= check_date
        ORDER BY when_created DESC
        ),
        check_date
      )
    AND
      h.when_created <  DATEADD(day, 1, param.check_date)
    AND
      h.action = 'checked_in'
GROUP BY
  param.check_date

  2. 0

    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

    https://dbfiddle.uk/RXx0x9xt

    Login or Signup to reply.
  3. 0

    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'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search