"SQL查詢特定日期範圍內有效的記錄"

Question

我正在進行出勤控制的工作。簽到和簽退是表中的兩個不同記錄

有可能有人幾天沒簽退，但應該算當天出勤

人員ID - 簽到時間 - 簽下時間

3842 12/17/2022 09:030 -- 從未簽約（從12/17.2022之後的每一天都應計算在內）

3843 12/17/2022 08:00 -- 12/17/2022 09:30. （下面是同一天）

3843 12/17/2022 11:00 -- 12/17/2022 13:30. （上面是同一天）

3841 12/17/2022 08:00 -- 12/17/2022 17:45. （簡單同一天）

3844 12/17/2022 22:00 -- 12/18/2022 6:40. （跨越午夜-應計算12/17和12/18）

我期望的結果是

12/15 1人
12/17 4人
12/18 2人

我想知道X天有多少人出勤

我有點困惑如何處理兩個不同的記錄（簽到和簽退）或只有一個記錄可用（簽到）

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;

一些插入語句

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)

Answer 1

通常，要測試這種情況，您需要查看您感興趣的日期是否大於入住日期並小於退房日期。例如

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

為了解決退房日期可以為空但仍「OK」的情況，我們只需用滿足條件的值替換空值。

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

Answer 2

由於要求變更，已刪除舊答案。在匆忙之中，沒有時間解釋。

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