DECLARE @MasterBatchEmailJobID int;
DECLARE @CommitteeId int;
DECLARE @DSCommitteeId int;
DECLARE @Unregistered int;
DECLARE @FirstRec int;
DECLARE @LastRec int;
SET @MasterBatchEmailJobID = 100246;
SET @CommitteeId = 58097;
SET @DSCommitteeId = 58097;
SET @Unregistered = 10;
SET @FirstRec = 1;
SET @LastRec = 40;
-- Debug Message --
-- Grid report: Email Performance Report: ENL FE 6/8/17 SNAP budget cuts --
-- Database Type --
-- StateDB,UCS, MyCampaign --

;WITH ConversionInfo AS (
SELECT MIN(cof.DateSubmitted) AS [FirstDateConverted], COUNT(cof.ContactsOnlineFormID) AS [ConversionCount], cof.BatchEmailJobDistributionID AS [BatchEmailJobDistributionID], cof.SubmittedContactsEmailID AS [SubmittedContactsEmailID]
FROM ContactsOnlineForms AS [cof]
WHERE (cof.SubmittedContactsEmailID IS NOT NULL)
GROUP BY cof.BatchEmailJobDistributionID, cof.SubmittedContactsEmailID

)
, ContributionInfo AS (
SELECT MIN(cc.DateReceived) AS [FirstDateReceived], COUNT(cc.ContactsContributionID) AS [ContributionCount], SUM(cc.Amount) AS [TotalAmount], cof.BatchEmailJobDistributionID AS [BatchEmailJobDistributionID], cof.SubmittedContactsEmailID AS [SubmittedContactsEmailID]
FROM (ContactsOnlineForms AS [cof]
JOIN ContactsContributions AS [cc]
ON (cc.ContactsOnlineFormID = cof.ContactsOnlineFormID))
WHERE (cof.SubmittedContactsEmailID IS NOT NULL)
GROUP BY cof.BatchEmailJobDistributionID, cof.SubmittedContactsEmailID

)
, a AS (
SELECT ContactsEmailBatches.ContactsEmailBatchID AS [ContactsEmailBatchID], CAST(DATEADD(hour, 0, bejd.DateSent) AS DATE) AS [DateSent], bejd.DateSent
  AS [DateSent_Sorted], ContactsEmailBatches.Email AS [Email], ContactsEmailBatches.VANID AS [VANID], CASE
WHEN ContactsDisplay.ContactModeID = 1 THEN
CASE WHEN ContactsDisplay.LastName IS NULL AND ContactsDisplay.FirstName IS NULL THEN
'[Unknown]'
ELSE
ISNULL(ContactsDisplay.LastName, '[Unknown]') + ', ' + ISNULL(ContactsDisplay.FirstName, '[Unknown]')
+ CASE WHEN ContactsDisplay.MiddleName IS NULL THEN '' ELSE ' ' + ContactsDisplay.MiddleName END
+ CASE WHEN ContactsDisplay.Suffix IS NULL THEN '' ELSE ' ' + ContactsDisplay.Suffix END
END
ELSE
COALESCE(ContactsDisplay.OrganizationContactCommonName, ContactsDisplay.OrganizationContactOfficialName, '[Unknown]')
END AS [ContactName], ContactsEmailBatches.VANID AS [ContactName_Filter], CASE WHEN ContactsEmailBatches.DateFirstRead IS NOT NULL THEN 1 ELSE 0 END AS [Opened], CASE WHEN ContactsEmailBatches.DateFirstClicked IS NOT NULL THEN 1 ELSE 0 END AS [Clicked], CASE WHEN ConversionInfo.ConversionCount > 0 THEN 1 ELSE 0 END AS [HasConverted], ContributionInfo.TotalAmount AS [ContributionInfoAmount], CASE WHEN ContactsEmailBatches.DateBounced IS NOT NULL THEN 1 ELSE 0 END AS [Bounced], CASE WHEN es.EmailSubscriptionID IS NOT NULL THEN 1 ELSE 0 END AS [Unsubscribed], bej.EmailSubject AS [EmailSubject], bej.BatchEmailJobName AS [BatchEmailJobName], bej.BatchEmailJobID AS [BatchEmailJobName_Filter]
FROM (((((((ContactsEmailBatches AS [ContactsEmailBatches] WITH (NOLOCK)
JOIN BatchEmailJobs AS [bej] WITH (NOLOCK)
ON (ContactsEmailBatches.BatchEmailJobID = bej.BatchEmailJobID))
JOIN MasterBatchEmailJobs AS [mbej] WITH (NOLOCK)
ON (mbej.MasterBatchEmailJobID = bej.MasterBatchEmailJobID))
LEFT JOIN EmailSubscriptions AS [es] WITH (NOLOCK)
ON (ContactsEmailBatches.Email = es.Email AND es.UnsubscribedByBatchEmailJobDistributionID = ContactsEmailBatches.BatchEmailJobDistributionID))
JOIN ContactsDisplay AS [ContactsDisplay] WITH (NOLOCK)
ON (ContactsEmailBatches.VanID = ContactsDisplay.VanID))
LEFT JOIN BatchEmailJobDistributions AS [bejd] WITH (NOLOCK)
ON (bejd.BatchEmailJobDistributionID = ContactsEmailBatches.BatchEmailJobDistributionID))
LEFT JOIN ConversionInfo AS [ConversionInfo] WITH (NOLOCK)
ON (ConversionInfo.BatchEmailJobDistributionID = ContactsEmailBatches.BatchEmailJobDistributionID AND ConversionInfo.SubmittedContactsEmailID = ContactsEmailBatches.ContactsEmailID))
LEFT JOIN ContributionInfo AS [ContributionInfo] WITH (NOLOCK)
ON (ContributionInfo.BatchEmailJobDistributionID = ContactsEmailBatches.BatchEmailJobDistributionID AND ContributionInfo.SubmittedContactsEmailID = ContactsEmailBatches.ContactsEmailID))
WHERE ((mbej.MasterBatchEmailJobID = @MasterBatchEmailJobID)
AND (ContactsEmailBatches.DateBlacklisted IS NULL)
AND (ContactsDisplay.CommitteeID = @DSCommitteeId)
AND (ContactsDisplay.VoterTypeID >= @Unregistered))

)
, b AS (
SELECT ContactsEmailBatchID AS [ContactsEmailBatchID], DateSent AS [DateSent], DateSent_Sorted AS [DateSent_Sorted], Email AS [Email], VANID AS [VANID], ContactName AS [ContactName], ContactName_Filter AS [ContactName_Filter], Opened AS [Opened], Clicked AS [Clicked], HasConverted AS [HasConverted], ContributionInfoAmount AS [ContributionInfoAmount], Bounced AS [Bounced], Unsubscribed AS [Unsubscribed], EmailSubject AS [EmailSubject], BatchEmailJobName AS [BatchEmailJobName], BatchEmailJobName_Filter AS [BatchEmailJobName_Filter], ROW_NUMBER() OVER (ORDER BY ContactsEmailBatchID DESC) AS [ReportRowNumber]
FROM a AS [b]

)
, c AS (
SELECT COUNT(1) AS [__total_rows__]
FROM a AS [c]

)
SELECT ContactsEmailBatchID AS [ContactsEmailBatchID], DateSent AS [DateSent], DateSent_Sorted AS [DateSent_Sorted], Email AS [Email], VANID AS [VANID], ContactName AS [ContactName], ContactName_Filter AS [ContactName_Filter], Opened AS [Opened], Clicked AS [Clicked], HasConverted AS [HasConverted], ContributionInfoAmount AS [ContributionInfoAmount], Bounced AS [Bounced], Unsubscribed AS [Unsubscribed], EmailSubject AS [EmailSubject], BatchEmailJobName AS [BatchEmailJobName], BatchEmailJobName_Filter AS [BatchEmailJobName_Filter], ( SELECT MAX(c.[__total_rows__]) AS [__total_rows__]
FROM c AS [c]
) AS [__total_rows__]
FROM b AS [d]
WHERE (ReportRowNumber BETWEEN @FirstRec AND @LastRec)


OPTION(RECOMPILE)