spGetAcknowledgementSummary

CREATE    procedure [dbo].[spGetAcknowledgementSummary]
( @EmailTemplateID int )
AS
BEGIN
DECLARE @Messages int
DECLARE @Sent int
DECLARE @Opened int
DECLARE @DSN int
DECLARE @SPAM int
DECLARE @Unsent int
DECLARE @UpdateDate datetime
DECLARE @InvalidAccount int
DECLARE @InactiveAccount int

    SELECT 

        @Messages    = isnull(COUNT(j.id),0),
        @Sent    = isnull(SUM(case r.Sent when 1 then 1 else 0 end),0),
        @Opened = isnull(SUM(case r.Opened when 1 then 1 else 0 end),0),
        @DSN    = isnull(SUM(case when r.DSNed =1 and r.RecentDSNCategory <> 112 then 1 else 0 end),0),
        @SPAM    = isnull(SUM(case when r.DSNed =1 and r.RecentDSNCategory = 112 then 1 else 0 end),0),
        @UpdateDate = isnull(MAX(r.UpdateDate),getutcdate())
    FROM EmailTemplate t
    INNER JOIN Email e on e.EmailTemplateID = t.[ID]
    INNER JOIN EmailJob j on j.EmailID = e.ID and j.[current] = 1
    LEFT OUTER JOIN EmailJob_Recipient as r ON r.EmailJobID = j.[ID] and r.[current] = 1
    WHERE t.[ID] = @EmailTemplateID 
    --AND   j.Status IN (4,6,7)


    SELECT 
        @InvalidAccount    = isnull(SUM(case r.InvalidAccount when 1 then 1 else 0 end),0),
    @InactiveAccount =  isnull(sum(case when isnull(r.Inactive,0) = 1 and (r.OptOut <> 1 and r.InvalidAddress <>1 ) then 1 else 0 end),0)
    FROM EmailTemplate t
    INNER JOIN Email e on e.EmailTemplateID = t.[ID]
    LEFT OUTER JOIN Email_Recipient as r ON r.EmailID = e.[ID]        
    WHERE t.[ID] = @EmailTemplateID     


    SET @UnSent = @Messages - @Sent
END

SELECT
    @EmailTemplateID EmailTemplateID, 
    @Messages Messages, @Sent Sent, @Unsent Unsent, 
    @Opened Opened, @DSN DSN, @UpdateDate UpdateDate, @InvalidAccount InvalidAccounts, @InactiveAccount InactiveAccounts, @SPAM SpamComplaint