Y26-107 - As a TL (Tom) I would like a billing_report_view_v2 to be created in the mlwh_reporting database using the latest billing query including stock plates information so that faculty can relate bills back to plates.

[TWJW-SANGER]

User story
As a TL (Tom), I would like a billing_report_view_v2 to be created in the mlwh_reporting database using the latest billing query, including stock plates information, so that faculty can relate bills back to plates.

Who are the primary contacts for this story
Tom W
Ivy T
Laura H
Rich R

Who is the nominated tester for UAT
We can test this ourselves.

Acceptance criteria
To be considered successful the solution must allow:

• A new database view is created in UAT and PROD environments for mlwh_reporting called billing_report_view_v2 backed by the new SQL query here.
• The query date range for the view should be set to the last 2 years at time of querying.
• The new query should be committed in sample_tracking/views with the correct placeholders so that the changes can be applied using apply.py to the different environments as described in the README.md
• The confluence page "Automated Billing Report" is updated to reference the new database view.

References
This story has a non-blocking relationship with:
sanger/General-Backlog-Items#686

Additional context
See the confluence page for details of the original billing query (without any stock plate column).
See this story for the development of the new billing query with the stock plate column sanger/General-Backlog-Items#686

[dn10-sanger]

Q. Re. updated_billing_query_inc_stock_plates.sql

1. Are custom_primer_used, kit_type, and cycle_number intentionally excluded from v2, or should they be carried over from v1?
2. Should qc_complete_date be included in the v2 output? It's computed in the query but not currently surfaced as a column.
3. Can a single sample be associated with more than one stock resource record? If so, what should happen, take the most recent, any, or should duplicates be handled explicitly?
4. Is the goal to deploy this as a view (like v1), a stored procedure, or both? The commented-out procedure wrapper suggests a stored procedure is planned, should that be the primary deliverable?

[TWJW-SANGER]

@dn10-sanger
Excellent questions.
Some answers below.

1. Are custom_primer_used, kit_type, and cycle_number intentionally excluded from v2, or should they be carried over from v1?
Good spot. This may indeed be something that has been missed in the cycle of refining the requirements between faculty, finance, and SciOps, or it may be they decided they didn't need it anymore. Rich Rance did have access to the new report specs and examples but possibly didn't notice. He is meant to be trying out the report for March, so I am going to check with him.

2. Should qc_complete_date be included in the v2 output? It's computed in the query but not currently surfaced as a column.
No, I think this is a hangover of debugging/checking exactly which runs are included. You are right it doesn't need to be in the SELECT statement.

3. Can a single sample be associated with more than one stock resource record? If so, what should happen, take the most recent, any, or should duplicates be handled explicitly?
Mostly no.
The relationship between the sample table and iseq_flowcell is one-to-one.
However, there are "compound" samples in the sample table that reflect "samples" made up of multiple child samples that are used in some pipelines doing RNA-Seq type workflows.
For the purposes of this billing report though, we do not need to dig into this (yet).

4. Is the goal to deploy this as a view (like v1), a stored procedure, or both? The commented-out procedure wrapper suggests a stored procedure is planned, should that be the primary deliverable?
It turned out the stored_procedure was never actually used (except indirectly) as Tableau is unable to call stored_procedures from MySQL, and unfortunately, Matt ended up copying the SQL query into Tableau.
I would rather Tableau called a view in mlwh_reporting, keeping the complex logic where we and other db users have access (separating the presentation and business layers).
So - view yes, stored procedure not required.

[KatyTaylor]

Just to add to 3)

Another instance where the 1:1 sample:stock_resource relationship is broken (the other way around) is in Bioscan, where the first 2 plates in the pipeline can both be registered as stock plates (LILYS and Lysate plates) - see sanger/sequencescape#4706 for where it was implemented. So that would be one sample with 2 stock plates.

e.g. SELECT * FROM stock_resource WHERE id_sample_tmp = 10198968;

[TWJW-SANGER]

@KatyTaylor very good point - so for Bioscan samples this will likely return at least 2 rows for each 'stock plate' in stock_resource in the sample_lanes CTE and go on to mess up the calculation.

Given that BioScan is for ToL and they are not yet using additional report, I suggest we go ahead with this story to create the view - but also:

• Create an additional user story to check the v2 billing report against Bioscan samples.
• Alert Rich R and other stakeholders to the issue.

Sound okay?

[dn10-sanger]

Completely understand and agree with it - happy to proceed with creating the view as-is. If you'd like I'll raise a separate story to validate the v2 billing report against Bioscan samples, and flag the issue to the relevant stakeholders. The fix can then be scoped once we know which stock plate is the meaningful one for billing in a Bioscan context.