os_cpr_spmn_stats_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

identifier

BIGINT

specimens_collected

BIGINT

specimens_missed

BIGINT

specimens_not_collected

BIGINT

specimens_pending

BIGINT

View Definition

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 ((((`openspecimen_demo`.`catissue_coll_prot_reg` `cpr` left join `openspecimen_demo`.`catissue_coll_prot_event` `cpe` on((`cpe`.`COLLECTION_PROTOCOL_ID` = `cpr`.`COLLECTION_PROTOCOL_ID`))) left join `openspecimen_demo`.`catissue_cp_req_specimen` `sr` on(((`sr`.`COLLECTION_PROTOCOL_EVENT_ID` = `cpe`.`IDENTIFIER`) and (`sr`.`ACTIVITY_STATUS` <> 'Disabled')))) left join `openspecimen_demo`.`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 `openspecimen_demo`.`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`

Columns

View Definition

Possibly Referenced Tables/Views