This document contains ad hoc database queries that can be used to generate additional reports from IRBWise. These queries are a resource provided in addition to those available in the IRBWIse query builder. To access custom queries in IRBWise:
It is possible to customize some of the queries below. For example, it is possible to change the date range on some queries. Copy the query into the IRBWise custom query window, then edit the query, following the instructions to change the dates given below.
Approved Protocols, by time period (for example, by month). This query allows the user to manually change the date range to show protocols approved during a certain period (for example, during the previous month). In the query to the right, there are 2 dates: the first one is the start date, the second is the end date. For example, to query approved protocols from July 1-August 1, 2008, do the following:
|
select protocol_number "PNO", sub_name "Submission Type", pi_name "PI", dept_short_name "Department", protocol_title "Title", to_char(orig_appr_start_date, 'YYYY-MM-DD') "Original Approval Start", to_char(curr_appr_start_date, 'YYYY-MM-DD') "Current Approval Start", to_char(curr_appr_end_date, 'YYYY-MM-DD') "Current Approval End", status_type_name "Current Status", review_type_name "Review Type", sttypes.status_type_name "Status Changed To", to_char(stat.status_sys_date, 'YYYY-MM-DD') "Status Change Recorded" from irb_protocol_report2_vw prt join irb_sub_status_hist stat on (prt.submission_id = stat.submission_id) join irb_status_types sttypes on (stat.status_type_id = sttypes.status_type_id) where stat.status_type_id = 8 and stat.status_sys_date between to_date('2007-01-01', 'YYYY-MM-DD') and to_date('2007-02-02', 'YYYY-MM-DD') order by stat.status_sys_date |
| Reviewer attendance at meetings (lists reviewer, meeting date, and whether they attended); data can be exported to excel, and then sorted to indicate what meetings a particular reviewer attended, or which members were present at a particular meeting |
select per.per_first_name || ' ' || per.per_last_name as reviewer, to_char(meet.meeting_date, 'YYYY-MM-DD HH24:MI') as meeting_date, per_attendance from irb_attendees att join irb_meetings meet on (att.meeting_id = meet.meeting_id) join irb_person_list per on (att.per_id = per.per_id) |
Reviewer Performance (lists reviewers, number, kind, whether full, expedited, or exempt, and the date the review was assigned and the date the review was completed. Analysis in excel allows calculation of the average time per reviewer or overall for all revewers during the time period. The query to the right shows the entire year, 2008-2009. But to change that to a different time period, for example, to query reviewer performance from July 1-August 1, 2008, do the following:
|
select distinct pl.per_first_name || ' ' || pl.per_last_name as reviewer, prv.protocol_number, prv.sub_name, prv.review_type_name review_type, to_char(spr.row_vstart_date, 'YYYY-MM-DD') as assigned, case (sr.review_complete) when 'Y' then to_char(sr.row_vstart_date, 'YYYY-MM-DD') else null end as completed from irb_sub_person_reviewers spr join irb_person_list pl on (spr.per_id = pl.per_id) join irb_protocol_report2_vw prv on (spr.submission_id = prv.submission_id) left join irb_sub_lead_reviewers slr on (spr.submission_id = slr.submission_id and spr.per_id = slr.per_id) left join irb_sub_reviews sr on (spr.submission_id = sr.submission_id and spr.per_id = sr.per_id) where spr.row_vstart_date between to_date('2008-01-01','YYYY-MM-DD') and to_date('2009-01-01','YYYY-MM-DD') |
| Approved protocols, organized by Departments |
select protocol_title "Title", protocol_number "IRB Tracking Number", pi_name "Investigator", to_char(orig_appr_start_date, 'YYYY-MM-DD HH:MI:SS AM') "Date of Initial Approval", fund_type_name "Type of Sponsor or Funding", review_type_name "Type of Initial Review", prv.dept_short_name "PI's Department", per_institution "PI's Institution", dl.dept_short_name "Protocol's Department", per_email "PI E-mail", per_email_secondary "PI Secondary E-mail" from irb_protocol_report2_vw prv left join irb_department_list dl on (prv.dept_id = dl.dept_id) left join irb_person_list pl on (prv.pi_per_id = pl.per_id) where sub_type_id = 1 and status_type_id = 8 |
| Approved protocols, organized by Locations |
select prv.submission_id, protocol_title "Title", protocol_number "IRB Tracking Number", pi_name "Investigator", to_char(orig_appr_start_date, 'YYYY-MM-DD HH:MI:SS AM') "Date of Initial Approval", fund_type_name "Type of Sponsor or Funding", review_type_name "Type of Initial Review", prv.dept_short_name "PI's Department", per_institution "PI's Institution", dl.dept_short_name "Protocol's Department", per_email "PI E-mail", per_email_secondary "PI Secondary E-mail", sll.study_loc_short_name "Study Location" from irb_protocol_report2_vw prv left join irb_department_list dl on (prv.dept_id = dl.dept_id) left join irb_person_list pl on (prv.pi_per_id = pl.per_id) left join irb_sub_locations sl on (prv.parent_submission_id = sl.submission_id) left join irb_study_loc_list sll on (sl.study_loc_id = sll.study_loc_id) where sub_type_id = 1 and status_type_id = 8 order by prv.submission_id |
| Funding amount (extensive data clean up required, because investigators entered explanatory text, and may not have entered a numerical dollar amount |
SELECT submission_id, protocol_number as "Protocol #", status_type_name as "Current Status", to_char(dbms_lob.substr(comment_notes,100,1)) as "Funding Amount" FROM irb_sub_ques_answers qa join irb_comments using (comment_id) join irb_submissions using (submission_id) join irb_sub_status using (submission_id) join irb_status_types using (status_type_id) WHERE question_id = -1305 and comment_notes is not null and dbms_lob.getlength(comment_notes) > 0 |
| Investigator and key study personnel emails listed in IRBWise |
select unique per_first_name, per_last_name, lower(per_email) email_address from irb_person_list where per_email is not null order by email_address |
| Select all emails listed in IRBWise |
select unique per_first_name, per_last_name, lower(per_email) email_address from irb_person_list where per_email is not null order by email_address |
| Select all personnel in IRBWise not associated with a study |
select submission_id, per_id, per_first_name, per_last_name from ((select unique submission_id, per_id from irb_sub_persons_assoc spa join irb_per_role_types prt on (spa.per_role_type_id = prt.per_role_type_id) join irb_submissions s on (spa.submission_id = s.submission_id) join irb_sub_saved ss on (spa.submission_id = ss.submission_id) where s.protocol_title is not null and s.sub_type_id = 1 and per_role_type_admin = 'Y') minus (select unique submission_id, per_id from irb_sub_persons_assoc spa join irb_per_role_types prt on (spa.per_role_type_id = prt.per_role_type_id) join irb_submissions s on (spa.submission_id = s.submission_id) join irb_sub_saved ss on (spa.submission_id = ss.submission_id) where s.protocol_title is not null and s.sub_type_id = 1 and per_role_type_admin = 'N')) rpt join irb_person_list using (per_id) order by submission_id, per_first_name, per_last_name |
| Select all records in IRBWise (for example, to analyze data in excel) |
select * from irb_protocol_report2_vw where sub_type_id = 1 and status_type_id = 8 |
| Select all IDE numbers (this query provides an overview of all device studies with IDE numbers; queries built in to IRBWise show studies with a particular device) |
select v.protocol_number "Protocol Number", v.device_ide_number "IDE Number", v.* from irb_device_report_vw v where status_type_id = 8 order by protocol_number; |
| Select all IND numbers (this query provides an overview of all drug studies with IND numbers; queries built in to IRBWise show studies with a particular drug) |
select v.protocol_number "Protocol Number", v.drug_ind_number "IND Number", v.* from irb_drug_report_vw v where status_type_id = 8 order by protocol_number |