UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS

Return number of actual or requested email sends

Return

Return Type
nvarchar(10)

Parameters

Parameter	Parameter Type	Mode
@STARTDATE	date	IN
@ENDDATE	date	IN
@EMAILSENTSTYPE	int	IN
@EMAILTYPE	int	IN

Definition

Copy



CREATE function dbo.UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS
(
 @STARTDATE date = null
,@ENDDATE date = null
,@EMAILSENTSTYPE integer = 1 --0: total of actual email sends, 1: total of requested email sends

,@EMAILTYPE integer = 1  --0: General purpose email jobs  1: Transactional email jobs  

)
returns nvarchar(10)
with execute as caller
as begin
    -- do work here and return a value

declare @NUMOFEMAILSENTS integer = 0
if @EMAILTYPE = 1
begin
      select @NUMOFEMAILSENTS = case @EMAILSENTSTYPE when 1 then SUM(REQUESTEDSENT) else SUM(ACTUALSENT) end
      from dbo.EMAILSTATUSTRANSACTIONAL S
      where S.STATUS in (3,7) and S.CLIENTPROCESSENDDATE >= ISNULL(@STARTDATE, '1/1/1900')  AND S.CLIENTPROCESSENDDATE < ISNULL(@ENDDATE, '12/31/2999') 
end
else
begin
      select @NUMOFEMAILSENTS=case @EMAILSENTSTYPE when 1 then SUM(REQUESTEDSENT) else SUM(ACTUALSENT) end
      from [dbo].[EMAILSTATUSGENERALPURPOSE] S  
      where S.STATUS in (3,7) and S.CLIENTPROCESSENDDATE >= ISNULL(@STARTDATE, '1/1/1900')  AND S.CLIENTPROCESSENDDATE < ISNULL(@ENDDATE, '12/31/2999') 
end

--select ISNULL(@NUMOFEMAILSENTS,0) as NUMOFEMAILSENTS  


    return ISNULL(@NUMOFEMAILSENTS,0)
end