Changes between Version 11 and Version 12 of OnyxDatabaseReporting

Timestamp:

02/13/13 11:39:01 (11 years ago)

Author:

Nick Holden

Comment:

--

OnyxDatabaseReporting

@@ -201 +201 @@
 ORDER BY start_date
 ;
+
+# To find duplicates enrollment_id entries:
+
+mysql -h briccsdb -u auditor -pg3s4t6 briccs -e "SELECT * FROM participant ptone WHERE EXISTS (SELECT 1 FROM participant pttwo WHERE pttwo.enrollment_id = ptone.enrollment_id LIMIT 1, 1 );"
+
+
+# So to exclude duplicates and NULL BPt numbers:
+
+mysql -h briccsdb -u auditor -pg3s4t6 briccs -e "SELECT * FROM participant ptone WHERE NOT EXISTS (SELECT 1 FROM participant pttwo WHERE pttwo.enrollment_id = ptone.enrollment_id LIMIT 1, 1 ) AND barcode is not null order by enrollment_id;"
+
+# To exclude duplicates, nulls and cancelled interviews and list barcodes (BPt numbers) and enrollment dates:
+
+mysql -h briccsdb -u auditor -pg3s4t6 briccs -e "SELECT barcode, 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 ptone.id = interview.participant_id order by start_date;" > briccs_enrollment_to_date.txt
+
+# To get a count of the above:
+
+mysql -h briccsdb -u auditor -pg3s4t6 briccs -e "SELECT count(*) 
+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 ptone.id = interview.participant_id order by start_date;"
+
+