USP_BBDW_FACT_MARKETINGCONSTITUENT_LOAD

create procedure dbo.[USP_BBDW_FACT_MARKETINGCONSTITUENT_LOAD](
  @OPENWINDOW datetime, 
  @CLOSEWINDOW datetime
) as
  set nocount on;

  select 
    cs.[MARKETINGCONSTITUENTID] as [MARKETINGCONSTITUENTFACTID],
    ss.[SEGMENTATIONID] as [SEGMENTATIONSYSTEMID],
    cs.[CONSTITUENTID] as [CONSTITUENTSYSTEMID],
    cs.[SEGMENTID] as [MKTSEGMENTATIONSEGMENTSYSTEMID],
    coalesce(cs.[TESTSEGMENTID], '00000000-0000-0000-0000-000000000000') as [MKTSEGMENTATIONTESTSEGMENTSYSTEMID],
    isnull(cs.[SOURCECODEMAPID], '00000000-0000-0000-0000-000000000000') as [MARKETINGSOURCECODESYSTEMID],
    cs.[FINDERNUMBER]
  from dbo.[CONSTITUENTSEGMENT] cs
  inner join dbo.[MKTSEGMENTATIONSEGMENT] ss on cs.[SEGMENTID] = ss.[ID]
  --Updated Appeal

  inner join dbo.[MKTSEGMENTATIONACTIVATE] sactivate on ss.[SEGMENTATIONID] = sactivate.[SEGMENTATIONID]
  --Updated Costs

  inner join dbo.[MKTSEGMENTATIONACTIVE] sactive on ss.[SEGMENTATIONID] = sactive.[ID]
  inner join dbo.[MKTSEGMENTATIONBUDGET] b on ss.[SEGMENTATIONID] = b.[ID]
  inner join (
    select [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID], max([MKTSEGMENTATIONPACKAGE].[DATECHANGED]) [DATECHANGED]
    from dbo.[MKTSEGMENTATIONPACKAGE] 
    group by [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID]
  ) as sp on ss.[SEGMENTATIONID] = sp.[SEGMENTATIONID]
  --Updated Marketing Constituent

  inner join dbo.[MKTSEGMENTATIONREFRESHPROCESS] r on ss.[SEGMENTATIONID] = r.[SEGMENTATIONID]
  where 
    (sactivate.[DATECHANGED] > @OPENWINDOW and sactivate.[DATECHANGED] <= @CLOSEWINDOW)
    or
    (sactive.[DATECHANGED] > @OPENWINDOW and sactive.[DATECHANGED] <= @CLOSEWINDOW)
    or
    (b.[DATECHANGED]> @OPENWINDOW and b.[DATECHANGED] <= @CLOSEWINDOW)
    or
    (sp.[DATECHANGED] > @OPENWINDOW and sp.[DATECHANGED] <= @CLOSEWINDOW)
    or
    (r.[DATECHANGED] > @OPENWINDOW and r.[DATECHANGED] <= @CLOSEWINDOW)
    or
    exists(
      select * 
      from dbo.[CONSTITUENTMERGEOPERATIONS] cmo 
      where 
        cmo.[TARGETID] = cs.[CONSTITUENTID] 
        and cmo.[DATEADDED] > @OPENWINDOW and cmo.[DATEADDED] <= @CLOSEWINDOW
    );