REDCap SQL query filtering on Instances

Question

In a REDCap (EAV table) project each record is a testing site.

Project is divided into two instruments. Instrument 1 will have information on the testing site (Address, DAG associated).

Instrument 2 is a repeatable instrument. Each instance will represent a date where testing is offered at that site.

I am trying to filter out sites using a sub query depending on the date testing is offer, i.e. the site will show on the list when we are between today and the testing date. I manage to filter out a whole record but I do not know how to filter only an instance of the record.

SELECT 
  value 
FROM redcap_data 
WHERE 
  project_id = 80 
  and 
  field_name = 'concat_site_date' 
  and 
  record in (
    SELECT
      record 
    FROM redcap_data 
    WHERE 
      project_id = 80 
      and 
      field_name ='date' 
      and
      value >= date(now())
  )

This filter out the record that has at least one instance where date >= date(now()) and shows both testing dates. However, one of the two instances is in the past and I wish to hide it. How best to add instances to filter in sql queries?

Answer 1

You want to know which sites have testing dates that are in the future, right?

I'd pull out the instance values that meet the time criterion (i.e. are in the future) and join that to a subquery that gives you the site-level data you want (i.e. fields form the non-repeating form):

select instancedata.record, sitefield1, sitefield2, instance, testingdate
from (
  select record, coalesce(instance, 1) as instance, value as testingdate
  from redcap_data
  where project_id=12188
  and field_name='testingdate'
  and now() < value
  group by project_id,record,event_id,instance
) instancedata
inner join (
  select record
  , group_concat(if(field_name='sitefield1',value,null)) as sitefield1
  , group_concat(if(field_name='sitefield2',value,null)) as sitefield2
  from redcap_data
  where project_id=12188 
  group by project_id,event_id,record
) recdata
on instancedata.record=recdata.record