UFN_MKTSEGMENTATIONSEGMENT_ISRECORDCOUNTCACHECURRENT_2

CREATE function dbo.[UFN_MKTSEGMENTATIONSEGMENT_ISRECORDCOUNTCACHECURRENT_2]
(
  @SEGMENTID uniqueidentifier,
  @DETECTEXTERNALCHANGES bit = 1,
  @DETECTPREVIOUSSEGMENTCHANGES bit = 1    /* This parameter should only be disabled in cases when we are already looping through all the segments in order. */
)
returns bit
as
begin
  declare @SEGMENTATIONID uniqueidentifier;
  declare @SEQUENCE int;
  declare @SEGMENTTYPECODE tinyint;
  declare @ISVENDORMANAGED bit;
  declare @ISHISTORICAL bit;
  declare @ISCURRENT bit;

  select
    @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
    @SEQUENCE = [MKTSEGMENTATIONSEGMENT].[SEQUENCE],
    @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
    @ISVENDORMANAGED = dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]),
    @ISHISTORICAL = [MKTSEGMENT].[ISHISTORICAL]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;


  if (@SEGMENTTYPECODE = 2 and @ISVENDORMANAGED = 1) or @SEGMENTTYPECODE in (6, 7, 8) or @ISHISTORICAL = 1 -- vendor managed segments, public media segments, historical segments

    set @ISCURRENT = 1;

  else if @SEGMENTTYPECODE = 3  --Revenue

    select @ISCURRENT = (
      case when exists(select top 1 *
                       from dbo.[MKTSEGMENTATIONSEGMENT]
                       left join dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] on [MKTSEGMENTATIONSEGMENTCACHEINFO].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
                       where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID
                       and ([MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] is null or [MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] < dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETLATESTREFRESHORCHANGEDATE]([MKTSEGMENTATIONSEGMENT].[ID], 1, @DETECTEXTERNALCHANGES)))
      then 0 else 1 end);

  else  --Constituent, membership, sponsorship or imported list (exclude vendor managed lists from this consideration)

    begin
      if @DETECTPREVIOUSSEGMENTCHANGES = 1
        --Detect changes from any previous segments too...

        select @ISCURRENT = (
          case when exists(select top 1 *
                           from dbo.[MKTSEGMENTATIONSEGMENT]
                           inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                           left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                           left join dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] on [MKTSEGMENTATIONSEGMENTCACHEINFO].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
                           where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                           and [MKTSEGMENTATIONSEGMENT].[SEQUENCE] <= @SEQUENCE
                           and ([MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] is null or [MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] < dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETLATESTREFRESHORCHANGEDATE]([MKTSEGMENTATIONSEGMENT].[ID], 1, @DETECTEXTERNALCHANGES))
                           and ([MKTSEGMENTLIST].[ID] is null or [MKTSEGMENTLIST].[TYPECODE] = 0))
          then 0 else 1 end);
      else
        --Only check the current segment for changes.  This should only be used in cases when we are already looping through all the segments in order.

        --So, this just avoids us from having the check the previous segments over and over again for each segment since we'll be checking them all anyhow.

        select @ISCURRENT = (
          case when exists(select top 1 *
                           from dbo.[MKTSEGMENTATIONSEGMENT]
                           left join dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] on [MKTSEGMENTATIONSEGMENTCACHEINFO].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
                           where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID
                           and ([MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] is null or [MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] < dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETLATESTREFRESHORCHANGEDATE]([MKTSEGMENTATIONSEGMENT].[ID], 1, @DETECTEXTERNALCHANGES)))
          then 0 else 1 end);
    end

  return @ISCURRENT;
end