ST0002 As a SSR (Rich C) I would like the sample tracking view to show all studies that have had manifests created in the last 2 years.

[TWJW-SANGER]

As a SSR (Rich C) I would like the sample tracking view to show all studies that have had manifests created in the last 2 years.

Acceptance Criteria

• [ ] Number of studies shown in the view should match the number of studies that have have had manifests created in the last 2 years.
• [ ] The view should contain entries for all the "Little Princess Knowledge Bank" studies that have received plates in the reporting period so that it matches what querying the stock_resource table directly and what is in SequenceScape (see below).
• [ ] The set of studies shown in the view should be a superset of the set of studies that have had plates created in the last 2 years.

Background The current method of looking at 'sample_manifest.updated' does not seem to capture everything expected. We would like to err on the side of displaying everything that has been started in the last 2 years.

Currently missing are the "The Little Princess" studies. Querying the stock_resource table in mlwarehouse as below shows plates and studies that are not appearing in the view.

SELECT DISTINCT study.name, sr.labware_human_barcode, sr.created
FROM stock_resource AS sr
  JOIN study ON (study.id_study_tmp=sr.id_study_tmp)
WHERE study.name LIKE '%Little%'
  AND sr.created >= DATE_SUB(NOW(), INTERVAL 2 YEAR)
ORDER BY study.name, sr.created

Truncated result gives:

name	labware_human_barcode	created
The Little Princess Knowledge Bank	SQPP-50963-V	2024-03-15 10:26:13
The Little Princess Knowledge Bank_Dupseq	SQPP-52768-F	2024-04-17 11:06:28
The Little Princess Knowledge Bank_RNAseq	SQPP-46775-P	2024-01-29 08:40:46
The Little Princess Knowledge Bank_RNAseq	SQPP-51367-L	2024-03-21 09:48:40

These plates are also visible in SequenceScape and Limber.

It's a little unclear why the existing logic does not work, it might be a bug in sequencescape or some variance in workflow. Once the number of studies shown is fixed - we will need another story to sanity check the columns drawing on the 'sample_manifest.updated' even

[khelwood]

Check how long the query takes to run.

[khelwood]

I increased the time windows in the queries to two years. I fixed the logic in sample_flowcell_view that I believe was causing missing rows. From a couple of tests, the revised sample_tracking_view would take about 15 minutes to execute.