USP_DATALIST_COMMITTEDREGISTRANTBATCHES

CREATE procedure dbo.USP_DATALIST_COMMITTEDREGISTRANTBATCHES
(
  @EVENTID uniqueidentifier,
  @NUMDAYS int = 7,
  @INCLUDEDELETED bit = 0
)
as
  set nocount on;

  select 
    BATCH.ID, 
    BATCH.BATCHNUMBER, 
    BATCH.DESCRIPTION, 
    (select case when DISPLAYNAME = '' then USERNAME else DISPLAYNAME end from dbo.APPUSER where ID = BATCH.APPUSERID) as OWNER,                            
    BATCH.STATUS,
    BATCHTYPECATALOG.CATEGORY,
    [BATCH].[DATECHANGED],
    coalesce(BATCHTYPECATALOG.BATCHDELETERECORDOPERATIONID, '5345164C-DD4F-4B6E-9CF1-10E1B25E3F4F') BATCHDELETERECORDOPERATIONID,
    BATCH.STATUSCODE,
    case
      when BATCH.STATUSCODE > 0 and BATCH.CREATECONTROLREPORT = 1 and len(BATCHTYPECATALOG.CONTROLREPORTREPORTSPECID) > 0 then
        1
      else 0
    end CONTROLREPORTVISIBLE,
    case
      when BATCH.STATUSCODE > 0 and len(BATCHTYPECATALOG.EXCEPTIONREPORTREPORTSPECID) > 0 then
        1
      else 0
    end EXCEPTIONREPORTVISIBLE,            
    case
        when (BATCH.ORIGINATINGBATCHID is not null) or ([CHILDBATCH].ID is not null) then 1
        else 0
    end INEXCEPTIONCHAIN,
    case when CUSTOM = 1 then '<Custom>' else BATCHTEMPLATE.NAME end as BATCHDESIGN ,
    [BATCH].[DATECOMMITTED]
  from dbo.BATCH
  left join dbo.BATCH [CHILDBATCH] on BATCH.ID = [CHILDBATCH].ORIGINATINGBATCHID
  inner join BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
  inner join BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
  inner join dbo.EventBatch eb on BATCH.ID = eb.ID and eb.EventID = @EVENTID
  where BATCH.STATUSCODE IN (1, 1 + @INCLUDEDELETED) AND BATCHTYPECATALOG.CATEGORY = 'Registrant' and not BATCH.DATECOMMITTED is null
  and (
    @NUMDAYS is null
    --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

    or (@NUMDAYS is not null and dateadd(ms, -003, dateadd(d, 1, cast(cast(BATCH.DATECOMMITTED as date) as datetime))) > dateadd(dd, -@NUMDAYS, getdate()))
    )
  and BATCH.DATECOMMITTED is not null
  order by BATCH.DATEADDED;

  return 0;