Experiment_Closure_Deployment_Script.sql

-- Script needs to be executed in the same order

-- Update experiment status to Stop if targeting is not enabled
update experiment as exp
set status = "TargetingDisabled"
where exp.`targeting_enabled` = 0 ;

-- Update experiment status to Running where targetting is enabled. Note : Should always run before Analyzing Script
update experiment as exp
set status = "TargetingEnabled"
where exp.`targeting_enabled` = 1;

-- Update experiment status to Analyzing, where at least one weighted type target has enabled tracking.

UPDATE experiment exp 
SET status="Analyzing" 
WHERE exp.`targeting_enabled` = 1 and
(select COUNT(*)  from target 
 inner join targeting_strategy tg on tg.target_id = target.`id`  where targeting_type = 'weighted_variation' and target.`experiment_id` = exp.id
 and target.`tracking_enabled` = 1
) > 0 ;

-- in order to sync up exp.iteration with iteration.iteration 
-- truncate and re-run the update
-- truncate table 
 truncate table iteration;
 
-- insert default iteration 
 insert into iteration (experiment_id,iteration,created_at)
 select exp.id, exp.`iteration`,  Now()  from experiment exp;

Note : Stale Experiment can fall into the following States : Running , Analysing or Stopped.

1. let's not conflate archived with the state. Not all archived experiments are closed (if they are unarchived for some reason we run into problems)
2. Line 26 should be "target.tracking_enabled = 1" and not equals 0

@pvonr Please review as it is updated as suggested.

Let's be save and use ) > 0 ; instead of ) = 1 ; on line 22

Thanks, That make sene.