Ilość osób, które mają wizytę w wybranym okresie czasu, ale więcej niż X ogólem

Hej, próbuje utworzyć zapytanie do bazy, które zwróci mi:

• ilość osób danego dnia, które:
  • mają wizytę w wybranym okresie czasu i :
  • będą to tylko te osoby, które ogółem mają więcej niż dwie wizyty

Mam dwie tabele: patient i visit. visit łączy się z patient po patientid. Przyjmujemy, że patient to pacjenci , a visit to wizyty tych pacjentów, co za tym idzie każdy pacjent może mieć X wizyt.
Mam problem z ograniczeniem tych dat. To co udało mi się do tej pory stworzyć (zapytanie nie zwraca żądanej wartości):

SELECT distinct(PatientID), Patient.firstname, patient.lastname, COUNT(distinct(PatientID)), count(*),
(SELECT count(*) FROM Visit v
JOIN Patient p ON v.patientID = p.id
WHERE v.x_removetime is NULL 
AND p.ID = Patient.Id
) AS a
FROM Patient
JOIN Visit ON Visit.patientID = patient.id
WHERE Visit.X_RemoveTime is NULL 
AND Visit.StartTime BETWEEN '2022-03-01' AND '2022-03-30'
GROUP by Patient.id
having count(*) > 2

Proszę o wskazówki lub jakąś inną pomoc :)

Dobry kierunek.

1. Łączsz pacjentów i wizyty po id pacjenta i zakresie dat wizity:

FROM Patient
JOIN Visit ON Visit.patientID = patient.id
WHERE Visit.X_RemoveTime is NULL 
AND Visit.StartTime BETWEEN '2022-03-01' AND '2022-03-30'

Można przenieść do warunku JOINA

FROM Patient
JOIN Visit ON Visit.patientID = patient.id 
AND Visit.X_RemoveTime is NULL 
AND Visit.StartTime BETWEEN '2022-03-01' AND '2022-03-30'

Czy to zapytanie zwraca Ci pacjentów mających wizyty w określonym przedziale czasu? Jeśli nie, to jakiego typu jest Visit.StartTime ?

2. Jeśli chcesz mieć ilość osób "danego dnia", to trzeba grupować również po dniu, a nie tylko po ID pacjenta.

@yarel: zmienna starttime jest typu datetime

muszę końcowo otrzymać jedną liczbę.

Chodzi mi o to, że np mamy dwóch pacjentów:

1. Anetek Bela, który ma wizyty:

   • 2022-02-13
   • 2022-02-28
   • 2022-03-23

2. Szymon Majewski, który ma wizyty:
   -2022-01-02
   -2022-03-01
   -2022-03-22

   Querka powinna zwrócić mi liczbę 1, bo tylko jeden pacjent spełnia warunki:

   • ma wizytę w dniu 2022-03-23

   • ogólnie ma więcej niż jedną wizytę

     zakładając taki zakres dat:

SELECT distinct(PatientID), Patient.firstname, patient.lastname, COUNT(distinct(PatientID)), count(*),
(SELECT count(*) FROM Visit v
JOIN Patient p ON v.patientID = p.id
WHERE v.x_removetime is NULL 
AND p.ID = Patient.Id
) AS a
FROM Patient
JOIN Visit ON Visit.patientID = patient.id
WHERE Visit.X_RemoveTime is NULL 
AND Visit.StartTime BETWEEN '2022-03-23 00:00:00' AND '2022-03-23 23:59:59'
GROUP by Patient.id
having count(*) > 2

Przy takim zapytaniu obecnie zwraca mi liczbę 0. Nie zwracaj uwagi na razie na to co jest w select (kolumny) - testowo sobie tak wprowadzam aby weryfikować poprawność danych pacjentów.

Pierwszy krok, to upewnienie się, że JOIN działą prawidłowo i dostajesz pacjenta i jego wizyty w określonym czasie. Dział Ci ten element, czy nie?

select 
patient.id 
FROM Patient
JOIN Visit ON Visit.patientID = patient.id
WHERE Visit.X_RemoveTime is NULL 
AND Visit.StartTime BETWEEN '2022-03-23 00:00:00' AND '2022-03-23 23:59:59'
GROUP by Patient.id
having count(*) > 2

W kolejnym kroku możesz policzyć takich pacjentów:

WITH  krok1 as (
select 
patient.id 
FROM Patient
JOIN Visit ON Visit.patientID = patient.id
WHERE Visit.X_RemoveTime is NULL 
AND Visit.StartTime BETWEEN '2022-03-23 00:00:00' AND '2022-03-23 23:59:59'
GROUP by Patient.id
having count(*) > 2
)
select count(distinct id) from krok1;

@yarel: właśnie chyba o to chodzi, że JOIN nie do końca działa poprawnie, bo zwraca mi liczbę tylko tych pacjentów, którzy w wybranym okresie czasu mają więcej niż dwie wizyty, a chodzi i o to aby sprawdzał czy ma wizyte w wybranym okresie czasu (jesli tak to sprawdzi ile ma wizyt w ogóle - jeśli więcej niż 2 to zwroci jego nazwisko, jeśli mniej to go pominie)

select 
patient.id 
FROM Patient
JOIN Visit ON Visit.patientID = patient.id
WHERE Visit.X_RemoveTime is NULL 
AND Visit.StartTime BETWEEN '2022-03-23 00:00:00' AND '2022-03-23 23:59:59'
GROUP by Patient.id
having count(*) > 2

Czyli zmieniają się warunki ;-)

Podejście to samo, budujemy w pierwszej kolejnosci pomocniczy zbiór danych o pacjentach i liczbie wizyt oraz liczbie wizyt w oknie czasowym.
W kolejnym kroku łączymy zbiór pacjentów z pomocnicznym zbiorem (po pacjencie) i filtrujemy po liczbie wizyt.

WITH liczba_wizyt_per_pacjent AS (
SELECT
  patient.id,
  SUM(CASE WHEN Visit.StartTime BETWEEN '2022-03-23 00:00:00' AND '2022-03-23 23:59:59' THEN 1 ELSE 0 END) liczba_wizyt_w_oknie_czasowym,
  count(0) laczna_liczba_wizyt
FROM Patient
JOIN Visit ON Visit.patientID = Patient.id AND Visit.X_RemoveTime IS NULL
GROUP BY Patient.id
)
SELECT 
  p.id,p.nazwisko 
FROM  Patient p 
JOIN  liczba_wizyt_per_pacjent pstats ON p.id = pstats.id
WHERE pstats.liczba_wizyt_w_oknie_czasowym>0 AND pstats.laczna_liczba_wizyt>2