Reset Forecasts, Scenario Versions and Mail Plans

reset.sql

USE usga_apps;
GO

TRUNCATE TABLE seer.forecasts;
GO

INSERT INTO seer.forecasts
SELECT *
FROM USGAPROD2.usga_apps.seer.forecasts f
WHERE EXISTS
(
  SELECT
    *
  FROM USGAPROD2.usga_apps.seer.forecast_snapshots fs
  INNER JOIN USGAPROD2.usga_apps.seer.scenario_versions sv ON sv.id = fs.scenario_version_id
  WHERE f.id = sv.forecast_id
    AND fs.id = '3c21d378-fd30-4c80-93f7-e5e3870748cc'
);
GO

TRUNCATE TABLE seer.scenario_versions;
GO

INSERT INTO seer.scenario_versions
SELECT *
FROM USGAPROD2.usga_apps.seer.scenario_versions sv
WHERE EXISTS
(
  SELECT
    *
  FROM USGAPROD2.usga_apps.seer.forecast_snapshots fs
  WHERE fs.scenario_version_id = sv.id
    AND fs.id = '3c21d378-fd30-4c80-93f7-e5e3870748cc'
);
GO

TRUNCATE TABLE seer.mail_plan_events;
GO

INSERT INTO seer.mail_plan_events
SELECT *
FROM USGAPROD2.usga_apps.seer.mail_plan_events mpe
WHERE EXISTS
(
  SELECT
    *
  FROM USGAPROD2.usga_apps.seer.forecast_snapshots fs
  INNER JOIN USGAPROD2.usga_apps.seer.scenario_versions sv ON sv.id = fs.scenario_version_id
  WHERE fs.scenario_version_id = sv.id
    AND mpe.scenario_version_id = sv.id
    AND fs.id = '3c21d378-fd30-4c80-93f7-e5e3870748cc'
);
GO

DECLARE @scenario_version TABLE (id uniqueidentifier NOT NULL PRIMARY KEY);

INSERT INTO seer.scenario_versions
(
   id
  ,forecast_id
  ,is_master_for_forecast
  ,most_recent_forecast_snapshot_id
  ,name
  ,commit_message
)
OUTPUT inserted.id INTO @scenario_version
SELECT
   NEWID() AS id
  ,forecast_id
  ,is_master_for_forecast
  ,most_recent_forecast_snapshot_id
  ,'Short Test' AS name
  ,'Only include drops between December 2014 and Febrary 2015' AS commit_message
FROM seer.scenario_versions
WHERE id = '1E0F9841-BC59-4AF8-AAE1-5E9529C0F444';

INSERT INTO seer.mail_plan_events
(
   id
  ,scenario_version_id
  ,event_id
  ,source_campaign
  ,drop_on
  ,targeted_expires_on
  ,targeted_activation_segment
  ,campaign
  ,technique
  ,initiative_month
  ,label
  ,expected_quantity
  ,expected_response_rate
  ,expected_avg_payment_amount
)
SELECT
   NEWID() AS id
  ,sv.id   AS scenario_version_id
  ,e.event_id
  ,e.source_campaign
  ,e.drop_on
  ,e.targeted_expires_on
  ,e.targeted_activation_segment
  ,e.campaign
  ,e.technique
  ,e.initiative_month
  ,e.label
  ,e.expected_quantity
  ,e.expected_response_rate
  ,e.expected_avg_payment_amount
FROM seer.mail_plan_events e
CROSS JOIN @scenario_version sv
WHERE e.scenario_version_id = '1E0F9841-BC59-4AF8-AAE1-5E9529C0F444'
  AND e.drop_on BETWEEN '2014-12-01' AND '2015-03-10';

INSERT INTO seer.forecast_snapshots
(
   id
  ,scenario_version_id
  ,is_most_recent_for_scenario_version
  ,started_at
  ,finished_at
)
OUTPUT inserted.id
SELECT
   NEWID() AS id
  ,sv.id   AS scenario_version_id
  ,0       AS is_most_recent_for_scenario_version
  ,NULL    AS started_at
  ,NULL    AS finished_at
FROM @scenario_version sv;
-- 8EE61733-1060-414F-B2D6-6C4A7BB19E3D