„SQL-Abfrage nach gültigen Datensätzen innerhalb eines bestimmten Datumsbereichs“

Question

Ich arbeite an der Anwesenheitskontrolle. Check-in und Check-out sind zwei verschiedene Datensätze in der Tabelle

Es ist möglich, dass sich jemand einige Tage lang nicht abgemeldet hat, dies sollte jedoch als Anwesenheit an diesem Tag gezählt werden

Personal-ID – Check-in-Zeit – Check-out-Zeit

3842 17.12.2022 09:030 – Nie ausgecheckt (jeder Tag nach dem 17.12.2022 sollte gezählt werden)

3843 17.12.2022 08:00 – 17.12.2022 09:30 (Das Folgende ist derselbe Tag)

3843 17.12.2022 11:00 – 17.12.2022 13:30 (Das Obige ist derselbe Tag)

3841 17.12.2022 08:00 – 17.12.2022 17:45 (Einfach am selben Tag)

3844 17.12.2022 22:00 – 18.12.2022 06:40 (Überschreitung von Mitternacht – 17.12. und 18.12. sollten gezählt werden)

Mein gewünschtes Ergebnis ist

15.12. 1 Person
17.12. 4 Personen
18.12. 2 Personen

Ich möchte wissen, wie viele Personen am X-Tag anwesend sind

Ich bin etwas verwirrt, wie ich mit zwei verschiedenen Datensätzen (Check-in und Check-out) oder nur einem verfügbaren Datensatz (Check-in) umgehen soll.

CREATE TABLE `my_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) DEFAULT NULL,
  `action` varchar(24) DEFAULT NULL,
  „when_created“-Zeitstempel NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMÄRSCHLÜSSEL („id“)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Einige Einfügeanweisungen

INSERT INTO `my_history` ( `person_id`, `action`, `when_created`)
WERTE
    (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');




TABELLE ERSTELLEN Person (
  idINT(11)
)

EINFÜGEN IN
  Person
WERTE
  (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