"Pertanyaan SQL untuk rekod yang sah dalam julat tarikh tertentu"

Question

Saya sedang mengusahakan kawalan kehadiran. Daftar masuk dan daftar keluar ialah dua rekod berbeza dalam jadual

Ada kemungkinan seseorang tidak melog keluar selama beberapa hari, tetapi ia harus dikira sebagai kehadiran pada hari tersebut

ID Personel - Masa daftar masuk - Masa daftar keluar

3842 17/12/2022 09:030 -- Tidak pernah mendaftar keluar (setiap hari selepas 17/12.2022 perlu dikira)

3843 17/12/2022 08:00 -- 17/12/2022 09:30 (Berikut adalah hari yang sama)

3843 17/12/2022 11:00 -- 17/12/2022 13:30 (Di atas adalah hari yang sama)

3841 17/12/2022 08:00 -- 17/12/2022 17:45 (Ringkas hari yang sama)

3844 17/12/2022 22:00 -- 18/12/2022 6:40 (Melintasi tengah malam - 17/12 dan 18/12 perlu dikira)

Hasil yang saya idamkan ialah

12/15 1 orang
12/17 4 orang
12/18 2 orang</pra>
<p>Saya ingin tahu berapa ramai orang yang hadir pada hari X</p>
<p>Saya agak keliru cara mengendalikan dua rekod berbeza (daftar masuk dan daftar keluar) atau hanya satu rekod tersedia (daftar masuk)</p>
<pre class="brush:php;toolbar:false;">BUAT JADUAL `my_history` (
  `id` int(11) BUKAN NULL AUTO_INCREMENT,
  `id_orang` int(11) LALAI NULL,
  `tindakan` varchar(24) LALAI NULL,
  cap waktu `apabila_dibuat` NULL CURRENT_TIMESTAMP NULL,
  KUNCI UTAMA (`id`)
) ENJIN=InnoDB AUTO_INCREMENT=1 CARSET lalai=utf8;

Beberapa penyata sisip

MASUKKAN KE DALAM `my_history` ( `person_id`, `action`, `when_created`)
NILAI
    (3842, 'mendaftar_masuk', '2022-12-15 08:00:00'),
    (3842, 'mendaftar_keluar', '2022-12-15 09:30:00'),
    (3842, 'mendaftar_masuk', '2022-12-17 09:30:00'),
    (3843, 'mendaftar_masuk', '2022-12-17 08:00:00'),
    (3843, 'mendaftar_keluar', '2022-12-17 09:30:00'),
    (3843, 'mendaftar_masuk', '2022-12-17 11:00:00'),
    (3843, 'mendaftar_keluar', '2022-12-17 13:30:00'),
    (3841, 'mendaftar_masuk', '2022-12-17 08:00:00'),
    (3841, 'mendaftar_keluar', '2022-12-17 17:42:00'),
    (3844, 'mendaftar_masuk', '2022-12-17 22:00:00'),
    (3844, 'mendaftar_keluar', '2022-12-18 06:40:00');




CIPTA MEJA orang (
  idINT(11)
)

MASUKKAN KE DALAM
  orang
NILAI
  (3841),
  (3842),
  (3843),
  (3844)

Answer 1

Lazimnya, untuk menguji keadaan ini, anda perlu melihat sama ada tarikh yang anda minati lebih besar daripada tarikh daftar masuk dan kurang daripada tarikh daftar keluar. Contohnya

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

Untuk menyelesaikan situasi di mana tarikh daftar keluar boleh kosong dan masih "OK", kami hanya menggantikan nilai kosong dengan nilai yang memenuhi syarat.

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

Answer 2

Jawapan lama telah dipadamkan kerana perubahan keperluan. Dalam tergesa-gesa, tidak ada masa untuk menjelaskan.

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