When updating Analysis record only set timestamp_completed value if it's not already set.

This PR is fixing the issue with timestamp_completed being updated even if it has been set prior. This is causing issues with seqr aggregation as it can not find any completed cram types prior to May 18, 2022 With more details reported in the close PR: https://github.com/populationgenomics/metamist/pull/871

There are 5614 records with wrong timestamp_completed.

There are 3 steps to fix this issue:

Apply this PR
Backup the records to be fixed with this sql:

CREATE TABLE analysis_backup_prior_timestamp_completed_update AS
SELECT analysis.*
FROM analysis
INNER JOIN (
SELECT id, MIN(timestamp_completed) as timestamp_completed 
FROM analysis FOR SYSTEM_TIME ALL 
WHERE timestamp_completed IS NOT NULL
GROUP BY id
) as s
on s.id = analysis.id AND analysis.timestamp_completed <> s.timestamp_completed;

Update existing records with the sql:

CREATE TABLE tmp_analysis_new_timestamp_completed AS
SELECT s.id, s.timestamp_completed as new_timestamp_completed
FROM analysis a
INNER JOIN (
SELECT id, MIN(timestamp_completed) as timestamp_completed 
FROM analysis FOR SYSTEM_TIME ALL 
WHERE timestamp_completed IS NOT NULL
GROUP BY id
) as s
on s.id = a.id AND a.timestamp_completed <> s.timestamp_completed;

UPDATE analysis
INNER JOIN tmp_analysis_new_timestamp_completed s
on s.id = analysis.id AND analysis.timestamp_completed <> s.new_timestamp_completed
SET analysis.timestamp_completed = s.new_timestamp_completed
WHERE analysis.id in (SELECT id from tmp_analysis_new_timestamp_completed);

populationgenomics / metamist

When updating Analysis record only set timestamp_completed value if it's not already set. #872

Codecov Report