USP_DATAFORMTEMPLATE_VIEW_UNDELIVEREDEMAIL

The load procedure used by the view dataform template "Undelivered Email Summary View"

Parameters

Parameter	Parameter Type	Mode	Description
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PREPARED	int	INOUT	Prepared
@PROCESSING	int	INOUT	Processing
@NOVALIDRECIPIENTS	int	INOUT	No Valid Recipients
@PROCESSED	int	INOUT	Processed
@UPLOADING	int	INOUT	Uploading
@UPLOADFAILED	int	INOUT	Upload failed
@LOCALEXCEPTION	int	INOUT	Local exception
@ACCEPTED	int	INOUT	Accepted
@SERVEREXCEPTION	int	INOUT	Server exception
@BOUNCED	int	INOUT	Bounced

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_UNDELIVEREDEMAIL
(
    @DATALOADED bit = 0 output,
    @PREPARED int = null output,
    @PROCESSING int = null output,
    @NOVALIDRECIPIENTS int = null output,
    @PROCESSED int = null output,
    @UPLOADING int = null output,
    @UPLOADFAILED int = null output,
    @LOCALEXCEPTION int = null output,
    @ACCEPTED int = null output,
    @SERVEREXCEPTION int = null output,
    @BOUNCED int = null output)
as
    set nocount on;

    set @DATALOADED = 1;

declare @CURRENTUTC datetime = getutcdate()

select 
    @PREPARED = isnull([PREPARED], 0), 
    @PROCESSING = isnull([PROCESSING], 0), 
    @NOVALIDRECIPIENTS = isnull([NOVALIDRECIPIENTS], 0), 
    @PROCESSED = isnull([PROCESSED], 0), 
    @UPLOADING = isnull([UPLOADING], 0), 
    @UPLOADFAILED = isnull([UPLOADFAILED], 0), 
    @LOCALEXCEPTION = isnull([LOCALEXCEPTION], 0), 
    @ACCEPTED = isnull([ACCEPTED], 0), 
    @SERVEREXCEPTION = isnull([SERVEREXCEPTION], 0), 
    @BOUNCED = ISNULL([BOUNCED], 0)
from (
    select 
        [2] as [PREPARED], 
        [3] as [PROCESSING], 
        [5] as [NOVALIDRECIPIENTS], 
        [12] as [PROCESSED], 
        [11] as [UPLOADING], 
        [13] as [UPLOADFAILED], 
        [8] as [LOCALEXCEPTION], 
        [4] as [ACCEPTED], 
        [14] as [SERVEREXCEPTION], 
        [7] as [BOUNCED]--7 is really Completed, but only the DSNed ones are counted 

    from(
        select EmailJob.[Status], 1 as One 
        from EmailJob 
        inner join Email 
            on EmailJob.EmailID = Email.ID
        left outer join EmailJob_Recipient
            on EmailJob.ID = EmailJob_Recipient.EmailJobID 
            and Email.[Type] = 1
        where (Email.[Type] in (1, 16))--1 = Acknowledgement, 16 = BBDM (for membership renewals)

            and (EmailJob.CreateDate > DATEADD(MONTH, -1, @CURRENTUTC))--should keep in sync with datalist 

            and (((EmailJob.[Status] = 2) and (EmailJob.CreateDate < DATEADD(MINUTE, -10, @CURRENTUTC)))--Prepared

                or ((EmailJob.[Status] = 3) and (EmailJob.CreateDate < DATEADD(MINUTE, -15, @CURRENTUTC)))--Processing

                or (EmailJob.[Status] = 5)--No Valid Recipients

                or ((EmailJob.[Status] = 12) and (EmailJob.CreateDate < DATEADD(MINUTE, -20, @CURRENTUTC)))--Processed

                or ((EmailJob.[Status] = 11) and (EmailJob.CreateDate < DATEADD(HOUR, -1, @CURRENTUTC)))--Uploading

                or (EmailJob.[Status] = 13)--Upload failed

                or (EmailJob.[Status] = 8)--Local exception

                or ((EmailJob.[Status] = 4) and (EmailJob.CreateDate < DATEADD(HOUR, -1, @CURRENTUTC)))--Accepted

                or (EmailJob.[Status] = 14)--Server exception

                or ((EmailJob.[Status] = 7) and (Email.[Type] = 1) and (EmailJob_Recipient.DSNed = 1))--Bounced (Delivery Status Notification)      

            ) 
    ) as src
    pivot(
        sum(One) for [Status] in ([2],[3],[5],[12],[11],[13],[8],[4],[14],[7])
    ) as pvt
) as out


    return 0;