USP_REPORT_EMAILSTATUSSUMMARYTRANSACTIONAL

Parameters

Parameter	Parameter Type	Mode
@JobDescription	nvarchar(1000)	IN
@EmailSubject	nvarchar(1000)	IN
@EmailRecipient	nvarchar(255)	IN
@Status	int	IN
@StartDate	datetime	IN
@EndDate	datetime	IN
@SortBy	int	IN
Definition

Copy


            CREATE procedure dbo.USP_REPORT_EMAILSTATUSSUMMARYTRANSACTIONAL
            (
                @JobDescription as nvarchar(1000),
                @EmailSubject as nvarchar(1000),
                @EmailRecipient as nvarchar(255),
                @Status as int,
                @StartDate as datetime,
                @EndDate as datetime,
                @SortBy int
            )
            as
            begin
                select
                    est.[STATUS]
                    ,COUNT(*) as EmailCount
                from
                    dbo.EMAILSTATUSTRANSACTIONAL est
                where    
                    ((@JobDescription = '') or (est.EMAILHEADER.value('(/EmailHeader/JobDescription)[1]','nvarchar(1000)') like (@JobDescription + '%')))
                    and ((@EmailSubject = '') or (est.EMAILHEADER.value('(/EmailHeader/Subject)[1]','nvarchar(1000)') like (@EmailSubject + '%')))
                    and ((@EmailRecipient = '') or (est.RECIPIENTADDRESS like (@EmailRecipient + '%')))
                    and ((@Status is null) or (est.[STATUS] = @Status))
                    and ((@StartDate is null) or (est.DATEADDED > dbo.UFN_DATE_GETEARLIESTTIME(@StartDate)))
                    and 
                        --Sort by DateAdded

                        ((@SortBy = 1 and 
                            ((@StartDate is null) or (est.DATEADDED >= dbo.UFN_DATE_GETEARLIESTTIME(@StartDate))) and
                            ((@EndDate is null) or (est.DATEADDED <= dbo.UFN_DATE_GETLATESTTIME(@EndDate))))
                        or
                        --Sort by DateChanged

                        (@SortBy = 2 and
                            ((@StartDate is null) or (est.DATECHANGED >= dbo.UFN_DATE_GETEARLIESTTIME(@StartDate))) and
                            ((@EndDate is null) or (est.DATECHANGED <= dbo.UFN_DATE_GETLATESTTIME(@EndDate)))))
                group by
                    est.[STATUS];    
            end