Select [sth] with at least two [activity] with gap time of at least 24 hours in mysql-CodePudding

Got a final project of developing a pandemic outbreak monitoring system database. other stuff I figured out pretty much, but this select statement I have no idea how to write:

List the phone numbers of all citizens who did two viral tests with the time window from 2021-10-03 00:00 to 2021-10-05 00:00. The two viral tests must have a gap time of at least 24 hours (at least 24 hours apart).

this is part of my diagram and is prob all that's needed for this particular question (ignore the doctor chart prob not needed here)

I was initially thinking maybe for each SSN count how many test IDs there are and then check if bigger than 2, but how's the 24 hr time gonna be calculated... and if 3 tests with like 20 hrs and 8 hrs in between then first and third is more than 24... anyways, u can hopefully see where I'm headed and how wrong I'm thinking lol. any help would be appreciated :)

CodePudding user response：

Could be this one:

SELECT a.ssn, MAX(a.phone) phone
FROM citizens a
JOIN patients b ON a.ssn = b.ssn
JOIN test c ON b.test_id = c.test_id
WHERE c.test_time BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00'
GROUP BY a.ssn
HAVING TIMESTAMPDIFF(HOUR, MIN(c.test_time), MAX(c.test_time)) >= 24

Sample data tested

CREATE TABLE citizens (
  ssn VARCHAR(20),
  phone VARCHAR(20)
);
CREATE TABLE patients (
  ssn VARCHAR(20),
  test_id INT
);
CREATE TABLE test (
  test_id INT,
  test_time DATETIME
);

INSERT INTO citizens VALUES ( 'A', '123' ), ( 'B', '456' ), ( 'C', '789' );
INSERT INTO patients VALUES ( 'A', 1 ), ( 'A', 2 ), ( 'A', 3 );
INSERT INTO patients VALUES ( 'B', 4 ), ( 'B', 5 ), ( 'B', 6 );
INSERT INTO patients VALUES ( 'C', 7 );

INSERT INTO test VALUES 
( 1, '2021-10-03 10:00:00' ),
( 2, '2021-10-03 20:00:00' ),
( 3, '2021-10-04 12:00:00' ),
( 4, '2021-10-03 10:00:00' ),
( 5, '2021-10-03 12:00:00' ),
( 6, '2021-10-03 19:00:00' ),
( 7, '2021-10-03 10:00:00' );