USP_DATALIST_DESIGNATIONCAMPAIGNFORDESIGNATION
Returns all of the campaigns and subpriorities that should be associated with a designation.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DESIGNATIONID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @ASOFDATE | datetime | IN | As of date |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_DESIGNATIONCAMPAIGNFORDESIGNATION (
@DESIGNATIONID uniqueidentifier,
@ASOFDATE datetime = null
) as begin
if @ASOFDATE is null
set @ASOFDATE = getdate();
select
DESIGNATIONCAMPAIGN.CAMPAIGNID,
CAMPAIGN.NAME [CAMPAIGNNAME],
DESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID,
CAMPAIGNPRIORITYTYPECODE.DESCRIPTION + ' - ' + CAMPAIGNSUBPRIORITYNAMECODE.DESCRIPTION as [CAMPAIGNSUBPRIORITYNAME]
from
dbo.DESIGNATIONCAMPAIGN
left outer join
dbo.CAMPAIGN on DESIGNATIONCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
left outer join
dbo.CAMPAIGNSUBPRIORITY on DESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID = CAMPAIGNSUBPRIORITY.ID
left outer join
dbo.CAMPAIGNPRIORITY on CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITY.ID
left outer join
dbo.CAMPAIGNPRIORITYTYPECODE on CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID = CAMPAIGNPRIORITYTYPECODE.ID
left join
dbo.CAMPAIGNSUBPRIORITYNAMECODE on CAMPAIGNSUBPRIORITYNAMECODE.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNSUBPRIORITYNAMECODEID
where
DESIGNATIONCAMPAIGN.DESIGNATIONID = @DESIGNATIONID
and
(DESIGNATIONCAMPAIGN.DATEFROM is null or DESIGNATIONCAMPAIGN.DATEFROM <= @ASOFDATE)
and
(DESIGNATIONCAMPAIGN.DATETO is null or @ASOFDATE <= DESIGNATIONCAMPAIGN.DATETO)
end