sqlsqlitedb-browser-sqlite

Time overlaps from Nesting queries

Based on the current schema I have been asked to find

Testing -- people who were untested and exposed to some one infectious -- Do not list anyone twice and do not list known sick people -- Exposed = at the same place, and overlap in time (No overlap time needed for simplicity)

From the query below I find my answer except I cannot remove the people who are 'postive' because the second part my query i.e the time lapse depends on the first part i.e the time the positive people went to the same locations.

select * from (
select DISTINCT person.PersonID, Register.LocID, Register.Checkin, Register.CheckOut
from person 
join Register on Person.PersonID = Register.PersonID 
join testing on person.PersonID  = testing.PersonID
where testing.Results is 'Positive' ) a 
join (
SELECT DISTINCT Person.PersonID, Register.LocID , Register.Checkin, Register.CheckOut
from person join Register on Person.PersonID = Register.PersonID 
where person.PersonID  
not in (SELECT DISTINCT testing.PersonID from testing)) b on a.LocID = b.LocID 
and b.checkin >= a.CheckIn and b.CheckIn <= a.CheckOut

So my question is, What modification does this query need to show the results of the results of the second part only?

I consider the first part to be

select * from (
select DISTINCT person.PersonID, Register.LocID, Register.Checkin, Register.CheckOut
from person 
join Register on Person.PersonID = Register.PersonID 
join testing on person.PersonID  = testing.PersonID
where testing.Results is 'Positive' ) a

And the second part to be

join (
SELECT DISTINCT Person.PersonID, Register.LocID , Register.Checkin, Register.CheckOut
from person join Register on Person.PersonID = Register.PersonID 
where person.PersonID  
not in (SELECT DISTINCT testing.PersonID from testing)) b on a.LocID = b.LocID 
and b.checkin >= a.CheckIn and b.CheckIn <= a.CheckOut
Solution

  • For readability you can create CTEs like this:

    with
  -- returns all the untested persons
  untested as (select p.* from person p left join testing t on t.personid = p.personid where t.testingid is null), 
  --  returns all the infected persons
  infected as (select * from testing where results = 'Positive'),
  -- returns all the locids that infected persons visited and the start and dates of these visits
  loc_positive as (
    select r.locid, i.timestamp startdate, r.checkout enddate 
    from register r inner join infected i 
    on i.personid = r.personid and i.timestamp between r.checkin and r.checkout
  )
-- returns the distinct untested persons that visited the same locids with persons tested positive at the same time after they were tested 
select distinct u.*
from untested u 
inner join register r on r.personid = u.personid
inner join loc_positive lp on lp.locid = r.locid 
where lp.startdate <= r.checkout and lp.enddate >= r.checkin