jackross
2/25/2015 - 2:51 AM

Compare Actual Mail Plan to Forecasted Mail Plan

Compare Actual Mail Plan to Forecasted Mail Plan

DECLARE @forecast_snapshot_id uniqueidentifier = '54266D12-8B13-4C23-9BDF-A740580ECFB2';

WITH _sources AS
(
  SELECT
     CASE
       WHEN source LIKE 'MB[QL]%'
       THEN LEFT(source, 7)
       ELSE LEFT(source, 9)
     END                               AS label
    ,technique
    ,COALESCE(mailed_on, '1999-12-31') AS dropped_on
    ,solicitations_count_source
  FROM usga_stage.dimensions.sources
  WHERE initiative_month >= '2014-08-01'
    AND source LIKE 'MB[^NU]%'
    AND solicitations_count_source > 0
)
,_actuals AS
(
  SELECT
     label
    ,technique
    ,dropped_on
    ,SUM(solicitations_count_source) AS sols_count
  FROM _sources
  GROUP BY
     label
    ,technique
    ,dropped_on
)
,_forecast AS
(
  SELECT
     label
    ,technique
    ,mail_plan_event_on           AS dropped_on
    ,SUM(simulated_targets_count) AS sols_count
  FROM usga_apps.seer.simulator_log
  WHERE forecast_snapshot_id = @forecast_snapshot_id
    AND technique IN ('MM', 'MW')
    AND simulated_targets_count > 0
  GROUP BY
     label
    ,technique
    ,mail_plan_event_on
)
,_union AS
(
  SELECT
     label
    ,technique
    ,dropped_on
  FROM _actuals
  UNION
  SELECT
     label
    ,technique
    ,dropped_on
  FROM _forecast
)
SELECT
   u.label
  ,u.technique
  ,u.dropped_on
  ,a.sols_count AS actuals_sols_count
  ,f.sols_count AS forecast_sols_count
FROM _union u
LEFT OUTER JOIN _actuals  a ON a.label = u.label AND a.technique = u.technique AND a.dropped_on = u.dropped_on
LEFT OUTER JOIN _forecast f ON f.label = u.label AND f.technique = u.technique AND f.dropped_on = u.dropped_on
ORDER BY
   label
  ,technique
  ,dropped_on
;