1 files changed, 53 insertions, 0 deletions
diff --git a/db/post_migrate/20220816163444_update_start_date_for_iterations_cadences.rb b/db/post_migrate/20220816163444_update_start_date_for_iterations_cadences.rb
new file mode 100644
index 00000000000..631e8941add
--- /dev/null
+++ b/db/post_migrate/20220816163444_update_start_date_for_iterations_cadences.rb
@@ -0,0 +1,53 @@
+# frozen_string_literal: true
+
+class UpdateStartDateForIterationsCadences < Gitlab::Database::Migration[2.0]
+  include ::Gitlab::Database::DynamicModelHelpers
+
+  restrict_gitlab_migration gitlab_schema: :gitlab_main
+  disable_ddl_transaction!
+
+  def up
+    each_batch_range('iterations_cadences', connection: connection) do |min, max|
+      execute(<<~SQL)
+        UPDATE iterations_cadences
+        SET start_date=ic.first_upcoming_iteration_start_date
+        FROM (
+          SELECT ic.id, sprints2.first_upcoming_iteration_start_date 
+          FROM iterations_cadences as ic,
+          LATERAL (
+            -- For each cadence, query for the due date of its current iteration
+            SELECT due_date as current_iteration_due_date FROM sprints
+            WHERE iterations_cadence_id=ic.id AND start_date <= current_date AND due_date >= current_date
+            LIMIT 1
+          ) as sprints1,
+          LATERAL (
+            -- For each cadence, query for the start date of the first upcoming iteration (i.e, it starts after the current iteration)
+            SELECT start_date as first_upcoming_iteration_start_date FROM sprints
+            WHERE iterations_cadence_id=ic.id AND start_date > sprints1.current_iteration_due_date
+            ORDER BY start_date ASC LIMIT 1
+          ) as sprints2
+          WHERE ic.automatic=true AND ic.id BETWEEN #{min} AND #{max}
+        ) as ic
+        WHERE iterations_cadences.id=ic.id;
+      SQL
+    end
+  end
+
+  def down
+    each_batch_range('iterations_cadences', connection: connection) do |min, max|
+      execute(<<~SQL)
+        UPDATE iterations_cadences
+        SET start_date=ic.first_iteration_start_date
+        FROM (
+          SELECT ic.id, sprints.start_date as first_iteration_start_date
+          FROM iterations_cadences as ic,
+            LATERAL (
+              SELECT start_date FROM sprints WHERE iterations_cadence_id=ic.id ORDER BY start_date ASC LIMIT 1
+            ) as sprints
+          WHERE ic.automatic=true AND ic.id BETWEEN #{min} AND #{max}
+        ) as ic
+        WHERE iterations_cadences.id=ic.id;
+      SQL
+    end
+  end
+end