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
;