UFN_MKTCOMMUNICATIONEFFORT_GETLASTRUNDATE_BULK

create function dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETLASTRUNDATE_BULK]()
returns table
as
return (
  with [BUSINESSPROCESS_CTE] ([PARAMETERSETID], [ENDEDON], [STATUSCODE], [ID]) as
  (
    select
      [BUSINESSPROCESSPARAMETERSETID],
      [ENDEDON],
      [STATUSCODE],
      [ID]
    from dbo.[BUSINESSPROCESSSTATUS]
  )
  select
    [MKTSEGMENTATION].[ID] as [SEGMENTATIONID],

    --Calculate

    [CALCULATEPROCESS_CTE].[LASTRUN] as [SEGMENTATIONSEGMENTCALCULATEPROCESSLASTRUN],

    --Refresh selections

    isnull([REFRESHSELECTIONS_CTE].[LASTRUN], [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN]) as [SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN],

    --Exclusions report

    [EXCLUSIONSREPORT_CTE].[LASTRUN] as [MARKETINGEXCLUSIONSREPORTLASTRUN],

    --SAR - If SAR was run during activate, then return the first activate date, else return the date SAR was cached during the segment counts calculate process

    isnull([SARACTIVATE_CTE].[FIRSTRUN], [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SOURCEANALYSISRULEDATALASTCACHED]) as [CACHESOURCEANALYSISRULEDATALASTRUN],

    --Export

    [EXPORTPROCESS_CTE].[LASTRUN] as [EXPORTPROCESSLASTRUN]

  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONSEGMENTREFRESHPROCESS] on [MKTSEGMENTATIONSEGMENTREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]

  --Calculate

  outer apply (select top 1 [BUSINESSPROCESS_CTE].[ENDEDON] as [LASTRUN] from [BUSINESSPROCESS_CTE] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] and [BUSINESSPROCESS_CTE].[STATUSCODE] = 0 order by [BUSINESSPROCESS_CTE].[ENDEDON] desc) as [CALCULATEPROCESS_CTE]

  --Refresh selections

  outer apply (select top 1 [BUSINESSPROCESS_CTE].[ENDEDON] as [LASTRUN] from [BUSINESSPROCESS_CTE] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTSEGMENTATIONSEGMENTREFRESHPROCESS].[ID] and [BUSINESSPROCESS_CTE].[STATUSCODE] = 0 order by [BUSINESSPROCESS_CTE].[ENDEDON] desc) as [REFRESHSELECTIONS_CTE]

  --Exclusions report

  outer apply (select top 1 [BUSINESSPROCESS_CTE].[ENDEDON] as [LASTRUN] from [BUSINESSPROCESS_CTE] inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID] = [BUSINESSPROCESS_CTE].[ID] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] and [BUSINESSPROCESSOUTPUT].[TABLEKEY] = 'EFFORTEXCLUSIONS' order by [BUSINESSPROCESS_CTE].[ENDEDON] desc) as [EXCLUSIONSREPORT_CTE]

  --SAR - This one is different in that it sorts by "asc" instead of "desc" because since you can run through the activate process more than once but SAR is only cached the very first time, we need to make sure we grab that very first activation date that cached the SAR (if it was cached during activate).

  outer apply (select top 1 [BUSINESSPROCESS_CTE].[ENDEDON] as [FIRSTRUN] from [BUSINESSPROCESS_CTE] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID] and [MKTSEGMENTATIONACTIVATEPROCESS].[CACHESOURCEANALYSISRULEDATA] = 1 and [BUSINESSPROCESS_CTE].[STATUSCODE] = 0 order by [BUSINESSPROCESS_CTE].[ENDEDON] asc) as [SARACTIVATE_CTE]

  --Export

  outer apply (select top 1 [BUSINESSPROCESS_CTE].[ENDEDON] as [LASTRUN] from [BUSINESSPROCESS_CTE] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTSEGMENTATIONEXPORTPROCESS].[ID] order by [BUSINESSPROCESS_CTE].[ENDEDON] desc) as [EXPORTPROCESS_CTE]
);