USP_DATALIST_REVENUEDEFAULTCAMPAIGN

CREATE procedure dbo.USP_DATALIST_REVENUEDEFAULTCAMPAIGN
(
  @DESIGNATIONID uniqueidentifier,
  @OPPORTUNITYID uniqueidentifier = null,
  @DATE datetime = null
)
as
  set nocount on;

  declare @RETVAL table (
    CAMPAIGNID uniqueidentifier, 
    CAMPAIGNSUBPRIORITYID uniqueidentifier
  );

  if @OPPORTUNITYID is not null
    begin
      insert into @RETVAL(CAMPAIGNID, CAMPAIGNSUBPRIORITYID)
        select distinct
          OPPORTUNITYDESIGNATIONCAMPAIGN.CAMPAIGNID,
          OPPORTUNITYDESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID
        from dbo.OPPORTUNITYDESIGNATION
        inner join dbo.OPPORTUNITYDESIGNATIONCAMPAIGN on OPPORTUNITYDESIGNATION.ID = OPPORTUNITYDESIGNATIONCAMPAIGN.OPPORTUNITYDESIGNATIONID
        inner join dbo.CAMPAIGN on CAMPAIGN.ID=OPPORTUNITYDESIGNATIONCAMPAIGN.CAMPAIGNID
        where 
          OPPORTUNITYDESIGNATION.OPPORTUNITYID = @OPPORTUNITYID
          and OPPORTUNITYDESIGNATION.DESIGNATIONID = @DESIGNATIONID
          and CAMPAIGN.ISACTIVE = 1
          --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);

      insert into @RETVAL(CAMPAIGNID, CAMPAIGNSUBPRIORITYID)
        select distinct
          DESIGNATIONCAMPAIGN.CAMPAIGNID,
          DESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID
        from dbo.DESIGNATIONCAMPAIGN
        inner join dbo.CAMPAIGN on CAMPAIGN.ID=DESIGNATIONCAMPAIGN.CAMPAIGNID
        where 
          DESIGNATIONCAMPAIGN.DESIGNATIONID = @DESIGNATIONID
          and    @DESIGNATIONID not in (select DESIGNATIONID from dbo.OPPORTUNITYDESIGNATION where OPPORTUNITYID = @OPPORTUNITYID)

          --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)

          and CAMPAIGN.ISACTIVE = 1;
    end
  else
    insert into @RETVAL(CAMPAIGNID, CAMPAIGNSUBPRIORITYID)
      select
        CAMPAIGNID,
        CAMPAIGNSUBPRIORITYID
      from dbo.UFN_DESIGNATION_GETDEFAULTCAMPAIGNS(@DESIGNATIONID, @DATE);


  select
    a.CAMPAIGNID,
    CAMPAIGN.NAME as CAMPAIGN,
    a.CAMPAIGNSUBPRIORITYID,
    CAMPAIGNSUBPRIORITY.NAME as CAMPAIGNIDSUBPRIORITY
  from @RETVAL a
  inner join CAMPAIGN on CAMPAIGN.ID = CAMPAIGNID
  left outer join CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = CAMPAIGNSUBPRIORITYID;

  return 0;