USP_DATALIST_ACTUALORREQUESTED_NUMBEROFRECIPIENTS

Return number of actual vs invalid email sent per job or all.

Parameters

Parameter	Parameter Type	Mode	Description
@STARTDATE	date	IN	Start date
@ENDDATE	date	IN	End date
@EMAILJOBTYPE	int	IN	Email job type
@SPECIFICDATE	date	IN	Specific date
@FILTERMODE	tinyint	IN	Select option

Definition

Copy



CREATE procedure dbo.USP_DATALIST_ACTUALORREQUESTED_NUMBEROFRECIPIENTS
(
@STARTDATE date = null
,@ENDDATE date = null
,@EMAILJOBTYPE integer = 0 --3: All jobs ,1: General purpose email jobs 2: Transactional email jobs

,@SPECIFICDATE date = null --'03/04/2011'

,@FILTERMODE tinyint = 0  --0: Today mode ,1: Specific day mode, 2: Date range Start and End date

)      
as
    set nocount on;

declare @TEMPJOBNAME nvarchar(100) = ''
declare @ACTUALSENT integer = 0
,@REQUESTEDSENT integer = 0
,@INVALIDEMAILS integer = 0

select @STARTDATE=STARTDATE, @ENDDATE=ENDDATE from dbo.UFN_WIDGETDASHBOARD_GETSTARTENDDATE(@STARTDATE,@ENDDATE,@SPECIFICDATE,@FILTERMODE)

--select @STARTDATE as start_date, @ENDDATE as end_date 


set @TEMPJOBNAME = case @EMAILJOBTYPE when 1 then 'General purpose email jobs' when 2 then 'Transactional email jobs' else 'All jobs' end
set @ACTUALSENT = (case @EMAILJOBTYPE when 3 then (coalesce(dbo.UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS(@STARTDATE,@ENDDATE,1,0),0) + coalesce(dbo.UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS(@STARTDATE,@ENDDATE,1,1),0))
                        when 2 then dbo.UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS(@STARTDATE,@ENDDATE,1,1)
                        else dbo.UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS(@STARTDATE,@ENDDATE,1,0) end)
set @REQUESTEDSENT = (case @EMAILJOBTYPE when 3 then (coalesce(dbo.UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS(@STARTDATE,@ENDDATE,0,0),0) + coalesce(dbo.UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS(@STARTDATE,@ENDDATE,0,1),0))
                        when 2 then dbo.UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS(@STARTDATE,@ENDDATE,0,1)
                        else dbo.UFN_GET_ACTUALORREQUESTED_NUMBEROFRECIPIENTS(@STARTDATE,@ENDDATE,0,0) end)

if (@REQUESTEDSENT > @ACTUALSENT)
  set @INVALIDEMAILS = @REQUESTEDSENT - @ACTUALSENT

select @TEMPJOBNAME as JOBNAME, 'Requested' as EMAILSENTSTYPE, @REQUESTEDSENT as NUMOFEMAILSENTS
union
select @TEMPJOBNAME as JOBNAME, 'Invalid addresses' as EMAILSENTSTYPE, @INVALIDEMAILS as NUMOFEMAILSENTS