UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT

Returns a list of campaigns to default onto a split based on the revenue's date and the split's designation.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@DESIGNATIONID	uniqueidentifier	IN
@DATE	datetime	IN

Definition

Copy



CREATE function dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT
(
  @DESIGNATIONID uniqueidentifier,
  @DATE datetime
)
returns table
as
return (
  select
    DESIGNATIONCAMPAIGN.CAMPAIGNID,
    DESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID
  from dbo.DESIGNATIONCAMPAIGN
  inner join dbo.CAMPAIGN on CAMPAIGN.ID = DESIGNATIONCAMPAIGN.CAMPAIGNID
  where CAMPAIGN.ISACTIVE = 1
  and DESIGNATIONCAMPAIGN.DESIGNATIONID = @DESIGNATIONID

  --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

  and (DESIGNATIONCAMPAIGN.DATEFROM is null or cast(DESIGNATIONCAMPAIGN.DATEFROM as date) <= @DATE)
  --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

  and (DESIGNATIONCAMPAIGN.DATETO is null or dateadd(ms, -003, dateadd(d, 1, cast(cast(DESIGNATIONCAMPAIGN.DATETO as date) as datetime))) >= @DATE)

  --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

  and (CAMPAIGN.STARTDATE is null or cast(CAMPAIGN.STARTDATE as date) <= @DATE)
  --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

  and (CAMPAIGN.ENDDATE is null or dateadd(ms, -003, dateadd(d, 1, cast(cast(CAMPAIGN.ENDDATE as date) as datetime))) >= @DATE)
);