USP_REPORT_EMAILJOBSPROCESSEDTRANSACTIONAL

CREATE procedure dbo.USP_REPORT_EMAILJOBSPROCESSEDTRANSACTIONAL
(
  @JobDescription as nvarchar(1000),
  @EmailSubject as nvarchar(1000),
  @EmailRecipient as nvarchar(255),
  @StartDate as datetime,
  @EndDate as datetime,
  @GroupType as int,
  @SortBy as int
)
as
begin
  select
    est.EMAILHEADER.value('(/EmailHeader/JobDescription)[1]','nvarchar(1000)') as JobDescription
    ,est.EMAILHEADER.value('(/EmailHeader/Subject)[1]','nvarchar(1000)') as EmailSubject
    ,est.RECIPIENTADDRESS as EmailRecipient
    ,est.DATECHANGED
    ,est.DATEADDED
    --Group type: 1 = Hourly, 2 = Daily, 3 = Monthly

    --Sort by: 1 = DateAdded, 2 = DateChanged

    ,case
      when ((@GroupType = 1) and (@SortBy = 1)) then dateadd(hour, datepart(hour, est.DATEADDED)/2, dbo.UFN_DATE_GETEARLIESTTIME(getdate()))
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      when ((@GroupType = 2) and (@SortBy = 1)) then cast(cast(est.DATEADDED as date) as datetime)
      when ((@GroupType = 3) and (@SortBy = 1)) then dateadd(day, 0, dateadd(month, datepart(month, est.DATEADDED) - 1, dateadd(year, 0, 0)))
      when ((@GroupType = 1) and (@SortBy = 2)) then dateadd(hour, datepart(hour, est.DATECHANGED)/2, dbo.UFN_DATE_GETEARLIESTTIME(getdate()))
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      when ((@GroupType = 2) and (@SortBy = 2)) then cast(cast(est.DATECHANGED as date) as datetime)
      when ((@GroupType = 3) and (@SortBy = 2)) then dateadd(day, 0, dateadd(month, datepart(month, est.DATECHANGED) - 1, dateadd(year, 0, 0)))
    end as GroupValue
  from 
    dbo.EMAILSTATUSTRANSACTIONAL est
  where
    est.[STATUS] = 3 --Completed jobs (maybe need more?)

    and ((@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 
      --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)))));
end