Changes between Version 5 and Version 6 of BRICCS CRLN Recruitment Report


Ignore:
Timestamp:
10/26/15 13:42:05 (9 years ago)
Author:
Richard Bramley
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • BRICCS CRLN Recruitment Report

    v5 v6  
    55== Summary
    66
    7 Report run at the start of each month summarizing all participants recruited into the [[BRICCS Study]] in the previous month.  Report does not include participants with a stus of {{{CANCELLED}}}.
     7Report run at the start of each month summarizing all participants recruited into the [[BRICCS Study]] in the current year - starting 1st of October.  Report does not include participants with a status of {{{CANCELLED}}}.
    88
    99== Format
    … …  
    2222 Server:: `UHLSQLBRICCSDB\UHLBRICCSDB`
    2323 Database:: `i2b2_app03_b1_data`
    24  Procedure:: `LastMonthCrlnBriccsRecruitmentReport`
    2524 Archive Directory:: {{{V:\BRICCS data requests\BRICCS recruitment data for CLRN}}}
     25 SQL::
     26{{{
     27#!sql
     28SELECT
     29          REPLACE(REPLACE(REPLACE([BPtNumber], 'b', 'B'), 'p', 'P'), 'T', 't') [ParticipantId]
     30      ,i2b2ClinDataIntegration.dbo.InitCap(REPLACE(REPLACE([RecruitingSite], 'briccs_', ''), '_recruitment', '')) [RecruitingSite]
     31      ,[InterviewDate] [EnrolmentDate]
     32FROM [i2b2_app03_b1_data].[dbo].[ParticipantRecruitment]
     33WHERE   InterviewDate >= '01-October-201?' AND   InterviewDate < '01-October-201? + 1'
     34}}}
    2635
    2736== Procedure (if output format approved by Angel)
    2837
    29 1. Log on to the database and run the stored procedure: `EXEC LastMonthCrlnBriccsRecruitmentReport`
    30 2. The stored procedure produces the recruitment report for the previous month.  So it must be run in the following month.  I.e., for June 2015 recruits it must be run in July 2015.
     381. Log on to the database and run the stored procedure and execute the SQL.
     392. You will need to identify the starting and end date.
    31403. Copy the output into a spreadsheet and save the spreadsheet in the archive directory with the name `BRICCS_Enrollment_{year}{month}.xls`.  E.g., `BRICCS_Enrollment_201506.xls`
    32414. Send the report to Angel.
    3342
    34 == Old Details - Delete when next process approved
    35 
    36  Recipient:: angel.christian@nihr.ac.uk
    37  Server:: {{{uhlbriccsapp01.xuhl-tr.nhs.uk}}}
    38  Working Directory:: {{{/home/nick/briccs_enrollment_reports/}}}
    39  Archive Directory:: {{{V:\BRICCS data requests\BRICCS recruitment data for CLRN}}}
    40  SQL Account:: Auditor
    41 
    42 == Old Procedure - Delete when next process approved
    43 
    44 === Create Participant Extracts
    45 
    46 1. Log onto the server and create the required extracts using the following MySql commands:
    47 
    48 ==== Northampton
    49 
    50 {{{
    51 #!sh
    52 mysql -h briccsdb -u auditor -p briccs_northampton -e "SELECT barcode, site_no, start_date, status FROM participant ptone, interview WHERE NOT EXISTS (SELECT 1 FROM participant pttwo WHERE pttwo.enrollment_id = ptone.enrollment_id LIMIT 1, 1 ) AND barcode is not null AND status <> 'CANCELLED' AND start_date > '2014-01-01 00:00:00' AND start_date < '2014-02-01 00:00:00' AND ptone.id = interview.participant_id order by start_date;" > briccs_enrollment_northampton_2014_01.txt
    53 }}}
    54 
    55 **Change dates in query and file name for the current month.**
    56 
    57 ==== Leicester
    58 
    59 {{{
    60 #!sh
    61 mysql -h briccsdb -u auditor -p redcap -e "SELECT pk,project_id,ts from redcap_log_event where event = 'INSERT' and object_type = 'redcap_data' and project_id = '24' and ts > '20141101000000' and ts < '20141201000000' order by ts;" > briccs_enrolment_leicester_2014_11.txt
    62 }}}
    63 
    64 **Change dates in query and file name for the current month.**
    65 
    66 ==== External REDCap sites
    67 
    68 {{{
    69 #!sh
    70 mysql -h briccsdb -u auditor -p redcap_briccsext -e "SELECT pk,project_id,ts from redcap_log_event where event = 'INSERT' and object_type = 'redcap_data' and project_id <> '12' and ts > '20141101000000' and ts < '20141201000000' order by ts;" > briccs_enrolment_external_sites_2014_11.txt
    71 }}}
    72 
    73 **Change dates in query and file name for the current month.**
    74 
    75 [[ToDo]]: Reference the required projects, as opposed to excluding the not required ones.
    76 
    77 == Post Process Files
    78 
    79 1. Copy the files from the working directory to the archive directory.
    80 2. Open the file {{{datetime conversion file.ods}}} from the archive directory in Libre Office.
    81 3. Open the Leicester and REDCap external extract files from the archive directory in Libre Office.
    82 4. Replace the existing data in the {{{datetime conversion file.ods}}} spreadsheet with the data from each file in turn. 
    83 5. Copy the list of data spreadsheet creates containing the site name and formatted date back into the extract file and save.
    84 6. Email the file to the recipient
    85 
    8643[[BackLinks]]