Y25-004 - As Ya-Lin H I would like a SQL query created and tested that can run against the SequenceScape database and produce a table of creation times for plates with 'RVI RT' purpose so that we can report the start times for RVI pipeline process.

[TWJW-SANGER]

User story

As Ya-Lin H I would like a SQL query created and tested that can run against the SequenceScape database and produce a table of creation times for plates with 'RVI RT' purpose so that we can report the start times for RVI pipeline process.

Who are the primary contacts for this story
Ya-Lin H
Osama M

Who is the nominated tester for UAT
Osama M

Acceptance criteria
To be considered successful the solution must allow:

• A tested SQL query that can be run read-only against the SequenceScape database that produces the output below for all plates with purpose 'RVI RT' within the study 'RVI Program - Bait Capture'
• An example csv file of output from the query.

Example Output

Sanger Sample ID	Supplier Sample Name	Plate Barcode	Well Location	Creation Time
RVI146952346	BRBR-331C4321S	SQPP-51846-V	B7	2024-04-02T11:55:07Z

Date time is represented in IS0 8601 format in UTC.

References
This story has a non-blocking relationship with:

• Osama M raising a FreshService ticket to gain read-only access to the SequenceScape production database.

Additional context
GSU would like to track the progress through their pipelines at a sample level.
They can use the mlwh_events database using similar queries to the plate level sample tracking that we provide to get most of the timesamps they need. BUT the RVI process start time is not Sample Reception but rather the creation of a plate with purpose 'RVI RT'

[KatyTaylor]

@TWJW-SANGER just checking this is still required, since library_start events are now appearing for this pipeline. I guess it is still required for the historical data? Thanks.

[TWJW-SANGER]

That's a good and interesting question. I am unsure.

I will check with the stakeholder and put this on hold.
Thank you.

[TWJW-SANGER]

Talking to Ya-Lin there still seem to be some issues for historic pipelines and the GBS data with timestamps not aligning to what they consider library_start. So this story is still considered required.

[BenTopping]

Query

USE sequencescape_development;

SELECT 
	samples.sanger_sample_id AS 'Sanger Sample ID',
	sample_metadata.supplier_name AS 'Supplier Sample Name',
	labware.name as 'Plate Barcode',
    maps.description as 'Well Location',
    labware.created_at as 'Creation Time'
FROM labware
-- Join plate purposes to be able to filter on plate purpose name
JOIN plate_purposes ON labware.plate_purpose_id = plate_purposes.id
-- Join receptacles to be able to access the wells
JOIN receptacles ON labware.id = receptacles.labware_id
-- Join maps to be able to filter on well position
JOIN maps ON receptacles.map_id = maps.id
-- Join aliquots to be able to access samples
JOIN aliquots ON receptacles.id = aliquots.receptacle_id
-- Join samples to be able to access sample data
JOIN samples ON aliquots.sample_id = samples.id
-- Join sample metadata to be able to access supplier sample name
JOIN sample_metadata ON samples.id = sample_metadata.sample_id
-- Join studies to be able to filter on Study name
JOIN studies ON aliquots.study_id = studies.id
-- Ensure labware type is plates only
WHERE labware.sti_type = 'Plate'
-- Ensure receptacles are all wells
AND receptacles.sti_type = 'Well'
-- Ensure plate purpose is RVI RT
AND plate_purposes.name = 'RVI RT'
-- Ensure we only get data from RVI studies
AND studies.name = 'RVI Program - Bait Capture';

Notes:

Use AND studies.name = 'UAT Study'; in replacement of the last line for UAT and local.
Use correct database name in USE clause at the top of the query.

Testing

4mins to execute on UAT

0.4 seconds to execute on Training

• 3359 rows. 4127 rows without study filter.
• Manual query via rails console found 43 'RVI RT' plates. Assuming 96 samples per plate 43x96 = 4128 wells/samples.