"Requête SQL pour les enregistrements valides dans une plage de dates spécifique"

Question

Je travaille sur le contrôle des présences. L'enregistrement et le départ sont deux enregistrements différents dans le tableau

Il est possible que quelqu'un ne se soit pas déconnecté pendant quelques jours, mais cela doit être comptabilisé comme une présence ce jour-là

ID personnel - Heure d'arrivée - Heure de départ

3842 17/12/2022 09:030 -- Jamais vérifié (tous les jours après le 17/12/2022 doivent être comptés)

3843 17/12/2022 08h00 -- 17/12/2022 09h30 (Ce qui suit est le même jour)

3843 17/12/2022 11h00 -- 17/12/2022 13h30 (Ce qui précède est le même jour)

3841 17/12/2022 08h00 -- 17/12/2022 17h45 (Simple le même jour)

3844 17/12/2022 22h00 -- 18/12/2022 6h40 (Le passage à minuit - le 17/12 et le 18/12 doivent être comptés)

Mon résultat souhaité est

15/12 1 personne
17/12 4 personnes
18/12 2 personnes</pré>
<p>Je souhaite savoir combien de personnes sont présentes le jour X</p>
<p>Je ne sais pas trop comment gérer deux enregistrements différents (enregistrement et départ) ou un seul enregistrement disponible (enregistrement)</p>
<pre class="brush:php;toolbar:false;">CREATE TABLE `mon_historique` (
  `id` int(11) NON NULL AUTO_INCREMENT,
  `person_id` int(11) NULL PAR DÉFAUT,
  `action` varchar(24) NULL PAR DÉFAUT,
  horodatage `when_created` NULL DEFAULT CURRENT_TIMESTAMP,
  CLÉ PRIMAIRE (`id`)
) MOTEUR=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Quelques instructions d'insertion

INSERT INTO `my_history` ( `person_id`, `action`, `when_created`)
VALEURS
    (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 personne (
  identifiantINT(11)
)

INSÉRER DANS
  personne
VALEURS
  (3841),
  (3842),
  (3843),
  (3844)

Answer 1

En général, pour tester cette situation, vous devez voir si la date qui vous intéresse est supérieure à la date d'arrivée et inférieure à la date de départ. Par exemple

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

Pour résoudre la situation où la date de départ peut être vide et toujours "OK", nous remplaçons simplement la valeur vide par une valeur qui remplit la condition.

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

Answer 2

L'ancienne réponse a été supprimée en raison d'un changement d'exigence. Dans la précipitation, je n’ai pas eu le temps de m’expliquer.

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