openspecimen Database

Parameters

Name	Type	Mode

Definition

begin
        drop table if exists tmp_os_cpr_spmn_stats;

create table
          tmp_os_cpr_spmn_stats
        as
          select
            cpr.identifier as identifier,
            count(distinct
              case when spmn.collection_status = 'Collected' then
                spmn.identifier
              else
                null
              end
            ) as specimens_collected,
            count(distinct
              case when spmn.collection_status = 'Missed Collection' then
                spmn.identifier
              else
                null
              end
            ) as specimens_missed,
            count(distinct
              case when spmn.collection_status = 'Not Collected' then
                spmn.identifier
              else
                null
              end
            ) as specimens_not_collected,
            count(distinct
              case when ((sr.identifier is not null and spmn.identifier is null) or spmn.collection_status = 'Pending') then
                case when (spmn.identifier is not null and spmn.req_specimen_id is null) then
                  concat('s', spmn.identifier)
                else
                  concat('sr', sr.identifier)
                end
              else
                null
              end
            ) as specimens_pending
          from
            catissue_coll_prot_reg cpr
            left join catissue_coll_prot_event cpe
              on cpe.collection_protocol_id = cpr.collection_protocol_id and cpe.activity_status != 'Disabled'
            left join catissue_cp_req_specimen sr
              on sr.collection_protocol_event_id = cpe.identifier and sr.activity_status != 'Disabled'
            left join catissue_specimen_coll_group visit
              on (visit.collection_protocol_event_id = cpe.identifier or visit.collection_protocol_event_id is null)
                and visit.collection_protocol_reg_id = cpr.identifier and visit.activity_status != 'Disabled'
            left join catissue_specimen spmn
              on (spmn.req_specimen_id is null or spmn.req_specimen_id = sr.identifier)
                and spmn.specimen_collection_group_id = visit.identifier and spmn.activity_status != 'Disabled'
          group by
            cpr.identifier;
      end

create_cpr_spmn_stats

Parameters

Definition