openspecimen Database

Parameters

Name	Type	Mode

Definition

begin
        insert into tmp_specimen_stats (
          specimen_id,
          collection_time, collection_procedure_id,
          collection_container_id, collector_id,
          received_time,
          received_quality_id, receiver_id,
          frozen_time, processing_time, ischemia_time,
          specimen_seq
        )

select
          c.identifier as specimen_id,
          ce.event_timestamp as collection_time, ce.collection_procedure_id as collection_procedure_id,
          ce.collection_container_id as collection_container_id, ce.user_id as collector_id,
          re.event_timestamp as received_time,
          re.received_quality_id as received_quality_id, re.user_id as receiver_id,
          fe.event_timestamp as frozen_time,
          timestampdiff(MINUTE, re.event_timestamp,fe.event_timestamp) as processing_time,
          timestampdiff(MINUTE, ce.event_timestamp, fe.event_timestamp) as ischemia_time,
          0
        from
          catissue_specimen s
          inner join catissue_coll_event_param ce on ce.specimen_id = s.identifier
          inner join catissue_received_event_param re on re.specimen_id = s.identifier
          inner join catissue_specimen_hierarchy h on h.ancestor_id = s.identifier
          inner join catissue_specimen c on c.identifier = h.descendent_id
          left join (
            select
              fre.object_id as specimen_id, max(fep.event_timestamp) as event_timestamp
            from
              catissue_frozen_event_param fep
              inner join catissue_form_record_entry fre on fre.record_id = fep.identifier
              inner join catissue_form_context ffc on ffc.identifier = fre.form_ctxt_id
              inner join dyextn_containers ff on ff.identifier = ffc.container_id and ff.name = 'SpecimenFrozenEvent'
            group by
              fre.object_id
          ) fe on fe.specimen_id = s.identifier
        where
          s.parent_specimen_id is null and
          s.collection_status = 'Collected' and
          s.activity_status != 'Disabled' and c.activity_status != 'Disabled' and
          c.collection_status = 'Collected';

update
          tmp_specimen_stats stats
          inner join (
            select
              identifier,
              dense_rank() over (partition by specimen_collection_group_id, parent_specimen_id, specimen_type_id order by identifier) as specimen_seq
            from
              catissue_specimen
            where
              activity_status = 'Active' and
              availability_status = 'Available'
          ) t on t.identifier = stats.specimen_id
        set
          stats.specimen_seq = t.specimen_seq;
      end

populate_specimen_stats

Parameters

Definition