USP_REPORT_EMAILSTATUSSUMMARYGENERALPURPOSE

Parameters

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

Copy


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

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

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