How do I select records from 21days (or more) ago? (sqlite)

Question

I'm trying to select patients who have received ONE dose of Comirnaty, AND are due for their next dose (vac_date was 21 days ago or more).

The following query got me everyone who has received ONE dose of Comirnaty:

SELECT NHI_id, fname, lname, vac_date
FROM Patients
NATURAL JOIN Vaccinations
NATURAL JOIN Vaccines
WHERE vaccine_name="Comirnaty"
GROUP BY NHI_id, fname, lname
HAVING count(vac_date)=1

NHI_id	fname	lname	vac_date
16120419	Colette	Carey	2021-04-15
16120427	Aretha	Livingston	2021-04-02
16120428	Clayton	Marsh	2021-03-31
16120433	Taylor	Buckley	2021-03-20

and this query selects everyone EXCEPT the one person due for their second dose:

SELECT NHI_id, vac_date
FROM Vaccinations
WHERE date("now", "-21 days") <= vac_date

NHI_id	vac_date
16120415	2021-04-10
16120419	2021-04-15
16120420	2021-04-15
16120421	2021-04-10
16120423	2021-04-01
16120424	2021-04-02
16120425	2021-04-02
16120426	2021-04-02
16120427	2021-04-02
16120428	2021-03-31
16120428	2021-04-01
16120430	2021-04-10
16120432	2021-04-15
16120434	2021-04-15
16120435	2021-04-15

So I tried combining the first query with the second using NOT IN, figuring that should give me the one case I'm looking for but I think I've overlooked something?

score 1 · Accepted Answer · answered Apr 16 '21 at 03:09

On recent versions of SQLite, I would use COUNT() here an analytic function:

WITH cte AS (
    SELECT NHI_id, fname, lname, vac_date,
           COUNT(*) OVER (PARTITION BY NHI_id, fname, lname) cnt
    FROM Patients
    NATURAL JOIN Vaccinations
    NATURAL JOIN Vaccines
    WHERE vaccine_name = 'Comirnaty'
)

SELECT NHI_id, fname, lname, vac_date
FROM cte
WHERE cnt = 1 AND vac_date < date('now', '-21 days');

The CTE above uses COUNT to generate a count of the number of vaccination records for each person. Then, in the following query, we restrict to persons only appearing once for the Comirnaty vaccine, as well as that single vaccination date having occurred 21 days or more from the current date.

How do I select records from 21days (or more) ago? (sqlite)

1 Answers1