V_OPPORTUNITYSOLICITOR

CREATE view dbo.V_OPPORTUNITYSOLICITOR
as 
  with OPPORTUNITYSOLICITOR_CTE as
  (
    select
      OPPORTUNITYSOLICITOR.OPPORTUNITYID as OPPORTUNITYID,
      case
        OPPORTUNITYSOLICITOR.FUNDRAISERTYPECODE
          when 0
            then PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID
          when 1
            then PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID
          when 2
            then SECONDARYFUNDRAISER.FUNDRAISERID
      end as FUNDRAISERID,
      OPPORTUNITYSOLICITOR.FUNDRAISERTYPECODE as FUNDRAISERTYPECODE
    from
      dbo.OPPORTUNITYSOLICITOR
    inner join
      dbo.OPPORTUNITY on OPPORTUNITY.ID = OPPORTUNITYSOLICITOR.OPPORTUNITYID
    inner join
      dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
    left join
      dbo.SECONDARYFUNDRAISER on SECONDARYFUNDRAISER.ID = OPPORTUNITYSOLICITOR.SECONDARYSOLICITORID
    where
      (OPPORTUNITYSOLICITOR.FUNDRAISERTYPECODE = 0 and (PROSPECTPLAN.PRIMARYMANAGERENDDATE is null or PROSPECTPLAN.PRIMARYMANAGERENDDATE > getdate())) or
      (OPPORTUNITYSOLICITOR.FUNDRAISERTYPECODE = 1 and (PROSPECTPLAN.SECONDARYMANAGERENDDATE is null or PROSPECTPLAN.SECONDARYMANAGERENDDATE > getdate())) or
      (OPPORTUNITYSOLICITOR.FUNDRAISERTYPECODE = 2 and (SECONDARYFUNDRAISER.DATETO is null or SECONDARYFUNDRAISER.DATETO > getdate()))
  ),
  OPPORTUNITY_CTE as
  (
    select
      OPPORTUNITY.ID,
      PROSPECTPLAN.ID as PROSPECTPLANID
    from
      dbo.OPPORTUNITY
    inner join
      dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
    where
      OPPORTUNITY.ID not in (select OPPORTUNITYID from OPPORTUNITYSOLICITOR_CTE)
  )

  --Primary managers on opportunities with no selected solicitors
  select
    OPPORTUNITY.ID as OPPORTUNITYID,
    PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID as FUNDRAISERID,
    0 as FUNDRAISERTYPECODE
  from
    dbo.PROSPECTPLAN
  inner join
    OPPORTUNITY_CTE OPPORTUNITY on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
  where
    PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID is not null and
    (PROSPECTPLAN.PRIMARYMANAGERENDDATE is null or PROSPECTPLAN.PRIMARYMANAGERENDDATE > getdate())

  union all

  --Secondary managers on opportunities with no selected solicitors
  select
    OPPORTUNITY.ID as OPPORTUNITYID,
    PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID as FUNDRAISERID,
    1 as FUNDRAISERTYPECODE
  from
    dbo.PROSPECTPLAN
  inner join
    OPPORTUNITY_CTE OPPORTUNITY on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
  where
    PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID is not null and
    (PROSPECTPLAN.SECONDARYMANAGERENDDATE is null or PROSPECTPLAN.SECONDARYMANAGERENDDATE > getdate())

  union all

  --Secondary fundraisers on opportunities with no selected solicitors
  select
    OPPORTUNITY.ID as OPPORTUNITYID,
    SECONDARYFUNDRAISER.FUNDRAISERID as FUNDRAISERID,
    2 as FUNDRAISERTYPECODE
  from
    dbo.SECONDARYFUNDRAISER
  inner join
    OPPORTUNITY_CTE OPPORTUNITY on OPPORTUNITY.PROSPECTPLANID = SECONDARYFUNDRAISER.PROSPECTPLANID
  where
    SECONDARYFUNDRAISER.DATETO is null or SECONDARYFUNDRAISER.DATETO > getdate()

  union all

  --All selected solicitors
  select
    OPPORTUNITYID,
    FUNDRAISERID,
    FUNDRAISERTYPECODE
  from
    OPPORTUNITYSOLICITOR_CTE