UFN_MKTCOMMUNICATIONEFFORT_GETSTATUSINFO_BULK

CREATE function dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETSTATUSINFO_BULK]()
returns table
as
return (
  with [BUSINESSPROCESS_CTE] ([PARAMETERSETID], [DATEADDED], [STATUSCODE]) as
  (
    select
      [BUSINESSPROCESSPARAMETERSETID],
      [DATEADDED],
      cast((case [STATUSCODE]
        when 0 then
          case when [NUMBEROFEXCEPTIONS] = 0 then
            0  --Completed

          else
            1  --Completed with exceptions

          end
        when 1 then 2  --Running

        when 2 then 3  --Did not finish

      end) as tinyint) as [STATUSCODE]
    from dbo.[BUSINESSPROCESSSTATUS]
  )
  select
    [MKTSEGMENTATION].[ID] as [SEGMENTATIONID],

    --STATUSCODE

    (case when [MKTSEGMENTATION].[ACTIVE] = 0 then
       (case when [CALCULATEPROCESS_CTE].[CALCULATEDATE] is null then
          1  --Setup started

        else
          (case when [EXPORTPROCESS_CTE].[EXPORTDATE] is null then
             2  --Counts generated

           else
             --If counts were run again after export, then status should be "counts generated" because we want the last status

             (case when [CALCULATEPROCESS_CTE].[CALCULATEDATE] > [EXPORTPROCESS_CTE].[EXPORTDATE] then
                2  --Counts generated

              else
                3  --File exported

              end)
           end)
        end)
     else
       (case when getdate() < isnull([MKTSEGMENTATION].[MAILDATE], [MKTSEGMENTATION].[ACTIVATEDATE]) then
          4  --Activated

        else
          5  --Past launch date

        end)
     end) as [STATUSCODE],

    [CALCULATEPROCESS_CTE].[CALCULATEDATE],
    [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] as [CALCULATEPROCESSID],

    [EXPORTPROCESS_CTE].[EXPORTDATE],
    [MKTSEGMENTATIONEXPORTPROCESS].[ID] as [EXPORTPROCESSID],

    [MKTSEGMENTATION].[ACTIVATEDATE],
    [MKTSEGMENTATIONACTIVATEPROCESS].[ID] as [ACTIVATEPROCESSID],

    [MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED],
    [MKTSEGMENTATIONREFRESHPROCESS].[ID] as [REFRESHPROCESSID],

    --ISCALCULATING

    cast((case when [MKTSEGMENTATION].[ACTIVE] = 0 and [CALCULATEPROCESS_CTE].[STATUSCODE] = 2 then 1 else 0 end) as bit) as [ISCALCULATING],

    --ISEXPORTING

    cast((case when [EXPORTPROCESS_CTE].[STATUSCODE] = 2 then 1 else 0 end) as bit) as [ISEXPORTING],

    --ISACTIVATING

    cast((case when [ACTIVATEPROCESS_CTE].[STATUSCODE] = 2 then 1 else 0 end) as bit) as [ISACTIVATING],

    --ISREFRESHING

    cast((case when [MKTSEGMENTATION].[ACTIVE] = 1 and [REFRESHPROCESS_CTE].[STATUSCODE] = 2 then 1 else 0 end) as bit) as [ISREFRESHING],

    --ISREMOVINGMEMBERS

    cast((case when [MKTSEGMENTATION].[ACTIVE] = 1 and [REMOVEMEMBERSPROCESS_CTE].[STATUSCODE] = 2 then 1 else 0 end) as bit) as [ISREMOVINGMEMBERS],

    [CALCULATEPROCESS_CTE].[STATUSCODE] as [CALCULATESTATUSCODE],
    [EXPORTPROCESS_CTE].[STATUSCODE] as [EXPORTSTATUSCODE],
    [ACTIVATEPROCESS_CTE].[STATUSCODE] as [ACTIVATESTATUSCODE],
    [REFRESHPROCESS_CTE].[STATUSCODE] as [REFRESHSTATUSCODE],

    [REMOVEMEMBERSPROCESS_CTE].[REMOVEMEMBERSDATE],
    [MKTUPDATEMAILINGCOUNTSPROCESS].[ID] as [REMOVEMEMBERSPROCESSID],
    [REMOVEMEMBERSPROCESS_CTE].[STATUSCODE] as [REMOVEMEMBERSSTATUSCODE]

  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTUPDATEMAILINGCOUNTSPROCESS] on [MKTUPDATEMAILINGCOUNTSPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  outer apply (select top 1 [BUSINESSPROCESS_CTE].[DATEADDED] as [CALCULATEDATE], [BUSINESSPROCESS_CTE].[STATUSCODE] from [BUSINESSPROCESS_CTE] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] order by [BUSINESSPROCESS_CTE].[DATEADDED] desc) as [CALCULATEPROCESS_CTE]
  outer apply (select top 1 [BUSINESSPROCESS_CTE].[DATEADDED] as [EXPORTDATE], [BUSINESSPROCESS_CTE].[STATUSCODE] from [BUSINESSPROCESS_CTE] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTSEGMENTATIONEXPORTPROCESS].[ID] order by [BUSINESSPROCESS_CTE].[DATEADDED] desc) as [EXPORTPROCESS_CTE]
  outer apply (select top 1 [BUSINESSPROCESS_CTE].[STATUSCODE] from [BUSINESSPROCESS_CTE] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID] order by [BUSINESSPROCESS_CTE].[DATEADDED] desc) as [ACTIVATEPROCESS_CTE]
  outer apply (select top 1 [BUSINESSPROCESS_CTE].[STATUSCODE] from [BUSINESSPROCESS_CTE] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTSEGMENTATIONREFRESHPROCESS].[ID] order by [BUSINESSPROCESS_CTE].[DATEADDED] desc) as [REFRESHPROCESS_CTE]
  outer apply (select top 1 [BUSINESSPROCESS_CTE].[DATEADDED] as [REMOVEMEMBERSDATE], [BUSINESSPROCESS_CTE].[STATUSCODE] from [BUSINESSPROCESS_CTE] where [BUSINESSPROCESS_CTE].[PARAMETERSETID] = [MKTUPDATEMAILINGCOUNTSPROCESS].[ID] order by [BUSINESSPROCESS_CTE].[DATEADDED] desc) as [REMOVEMEMBERSPROCESS_CTE]
);