UFN_MKTSEGMENTATIONSEGMENT_GETLATESTREFRESHORCHANGEDATE

CREATE function dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETLATESTREFRESHORCHANGEDATE]
(
  @SEGMENTID uniqueidentifier,
  @INCLUDEFILTERS bit = 0,
  @DETECTEXTERNALCHANGES bit = 1
)
returns datetime
as
begin
  declare @RETURNDATE datetime
  declare @SEGMENTTYPECODE tinyint;
  declare @DATECHANGED datetime;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @MKTSEGMENTID uniqueidentifier;
  declare @MKTSEGMENTDATEREFRESHED datetime;
  declare @MKTSEGMENTDATECHANGED datetime;
  declare @ADDRESSPROCESSINGOPTIONDATECHANGED datetime;
  declare @PACKAGEID uniqueidentifier;  

  if @INCLUDEFILTERS is null
    set @INCLUDEFILTERS = 0;

  select
    @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
    @MKTSEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
    @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
    @PACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

  if @SEGMENTTYPECODE = 3  --Revenue segments

    begin
      select @RETURNDATE = max([T].[DATE])
      from (
        --Get the last date that a record was deleted from the MKTREVENUELETTER tables for this mailing

        (select max([AUDITDATE]) as [DATE] from dbo.[V_MKTREVENUELETTERAUDIT] where [MKTSEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTID] = @MKTSEGMENTID and [MKTPACKAGEID] = @PACKAGEID and [AUDITTYPECODE] = 2)
        union all
        --If no records were deleted from the MKTREVENUELETTER table, then return the minimum date instead of null

        (select cast('1753-01-01T00:00:00.000' as datetime) as [DATE])
      ) as [T];
    end
  else
    begin
      select
        @DATECHANGED = (case when [MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] is null then [MKTSEGMENTATIONSEGMENT].[DATECHANGED] else null end),
        @MKTSEGMENTDATEREFRESHED = [MKTSEGMENT].[DATEREFRESHED],
        @MKTSEGMENTDATECHANGED = [MKTSEGMENT].[DATECHANGED],
        @ADDRESSPROCESSINGOPTIONDATECHANGED = [ADDRESSPROCESSINGOPTION].[DATECHANGED]        
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
      left outer join dbo.[ADDRESSPROCESSINGOPTION] on [ADDRESSPROCESSINGOPTION].[ID] = (case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONID] else [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID] end)
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left join dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] on [MKTSEGMENTATIONSEGMENTCACHEINFO].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

      --Get the latest date that the segment could have been modified...

      select @RETURNDATE = max([T].[DATE])
      from (
        --Get the date changed for the mailing segment

        (select @DATECHANGED as [DATE])
        -- this is deliberately no longer being checked, now that clients can effectively lock down a marketing effort pre-activation

        union all
        --Get the refresh date of the global segment

        (select case when @DETECTEXTERNALCHANGES = 1 then @MKTSEGMENTDATEREFRESHED else cast('1753-01-01T00:00:00.000' as datetime) end)
        union all
        --Get the date changed for the global segment in case they add or remove a selection

        -- this may not be checked, now that clients want to be able to effectively lock down a marketing effort pre-activation

        (select case when @DETECTEXTERNALCHANGES = 1 then @MKTSEGMENTDATECHANGED else cast('1753-01-01T00:00:00.000' as datetime) end)
        union all
        --Get the date changed for all the selections in the segment (determines when each selection was last refreshed)

        -- this may not be checked, now that clients want to be able to effectively lock down a marketing effort pre-activation

        (select case when @DETECTEXTERNALCHANGES = 1 then max([DATECHANGED]) else cast('1753-01-01T00:00:00.000' as datetime) end from dbo.[IDSETREGISTER] where [ID] in (select [SELECTIONID] from dbo.[MKTSEGMENTSELECTION] where [SEGMENTID] = @MKTSEGMENTID))
        union all
        --Get the date changed for the address processing option set selected for the mailing

          -- this may not be checked, now that clients want to be able to effectively lock down a marketing effort pre-activation

        (select case when @DETECTEXTERNALCHANGES = 1 then @ADDRESSPROCESSINGOPTIONDATECHANGED else cast('1753-01-01T00:00:00.000' as datetime) end)
      ) as [T];

      --Get the latest date that either the segment or any filters on the mailing could have been modified...

      if @INCLUDEFILTERS = 1
        begin
          select @RETURNDATE = max([T].[DATE])
          from (
            --Get the segment's latest date from above

            (select @RETURNDATE as [DATE])
            union all
            --Get the date changed for all the selections in the universe/exclusions...

            (select max([DATECHANGED]) from dbo.[MKTSEGMENTATIONFILTERSELECTION] where [SEGMENTATIONID] = @SEGMENTATIONID)
            union all
            --Get the last date that a selection in the universe/exclusions was deleted...

            (select max([AUDITDATE]) from dbo.[MKTSEGMENTATIONFILTERSELECTIONAUDIT] where [SEGMENTATIONID] = @SEGMENTATIONID and [AUDITTYPECODE] = 2)
            union all
            --Get the date changed for all the selections in the universe/exclusions (determines when each selection was last refreshed)

            -- this may not be checked, now that clients want to be able to effectively lock down a marketing effort pre-activation

            (select case when @DETECTEXTERNALCHANGES = 1 then max([DATECHANGED]) else cast('1753-01-01T00:00:00.000' as datetime) end from dbo.[IDSETREGISTER] where [ID] in (select [SELECTIONID] from dbo.[MKTSEGMENTATIONFILTERSELECTION] where [SEGMENTATIONID] = @SEGMENTATIONID))
            union all
            --Get the date changed for all the previous mailing exclusions...

            (select max([DATECHANGED]) from dbo.[MKTSEGMENTATIONFILTERSEGMENTATION] where [SEGMENTATIONID] = @SEGMENTATIONID)
            union all
            --Get the last date that a previous mailing exclusion was deleted...

            (select max([AUDITDATE]) from dbo.[MKTSEGMENTATIONFILTERSEGMENTATIONAUDIT] where [SEGMENTATIONID] = @SEGMENTATIONID and [AUDITTYPECODE] = 2)
          ) as [T];
        end
    end

  return @RETURNDATE;
end