USP_REPORT_EMAILJOBSPROCESSEDGENERALPURPOSE

CREATE procedure dbo.USP_REPORT_EMAILJOBSPROCESSEDGENERALPURPOSE
(
  @JobDescription as nvarchar(1000),
  @EmailSubject as nvarchar(1000),
  @StartDate as datetime,
  @EndDate as datetime,
  @GroupType as int,
  @SortBy as int
)
as
begin
  select
    esg.EMAILHEADER.value('(/EmailHeader/JobDescription)[1]','nvarchar(1000)') as JobDescription
    ,esg.EMAILHEADER.value('(/EmailHeader/Subject)[1]','nvarchar(1000)') as EmailSubject
    ,esg.DATECHANGED
    ,esg.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, esg.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(esg.DATEADDED as date) as datetime)
      when ((@GroupType = 3) and (@SortBy = 1)) then dateadd(day, 0, dateadd(month, datepart(month, esg.DATEADDED) - 1, dateadd(year, 0, 0)))
      when ((@GroupType = 1) and (@SortBy = 2)) then dateadd(hour, datepart(hour, esg.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(esg.DATECHANGED as date) as datetime)
      when ((@GroupType = 3) and (@SortBy = 2)) then dateadd(day, 0, dateadd(month, datepart(month, esg.DATECHANGED) - 1, dateadd(year, 0, 0)))
    end as GroupValue
  from 
    dbo.EMAILSTATUSGENERALPURPOSE esg
  where
    esg.[STATUS] = 3 --Completed jobs (maybe need more?)

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