UFN_APPEAL_SOLICITEDCOUNTS

Returns a table of appeal IDs and the number of people solicited for each within the given date range.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN

Definition

Copy



CREATE function dbo.[UFN_APPEAL_SOLICITEDCOUNTS]
(
  @STARTDATE datetime,
  @ENDDATE datetime
)
returns table
as
return (
  select
    [APPEAL].[ID] as [APPEALID],
    isnull((select sum([MKTSEGMENTATIONACTIVE].[QUANTITY])
            from dbo.[MKTSEGMENTATIONACTIVE]
            inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVE].[ID] and [MKTSEGMENTATION].[ACTIVE] = 1
            inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0' and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] = cast([APPEAL].[ID] as nvarchar(36))
            where (@STARTDATE is null or isnull([MKTSEGMENTATION].[MAILDATE], [MKTSEGMENTATION].[ACTIVATEDATE]) >= dbo.[UFN_DATE_GETEARLIESTTIME](@STARTDATE))
            and (@ENDDATE is null or isnull([MKTSEGMENTATION].[MAILDATE], [MKTSEGMENTATION].[ACTIVATEDATE]) <= dbo.[UFN_DATE_GETLATESTTIME](@ENDDATE))
           ), 0) as [SOLICITEDCOUNT]
  from dbo.[APPEAL]
);