USP_MKTSEGMENTATION_GETFIRSTRESPONSEDATE

create procedure dbo.[USP_MKTSEGMENTATION_GETFIRSTRESPONSEDATE]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @FIRSTRESPONSEDATE date;
  declare @ACTIVE bit;
  declare @RECORDSOURCEGIFTIDSETID uniqueidentifier;
  declare @RECORDSOURCEFIRSTRESPONSEDATE date;
  declare @CURRENTDATE datetime;
  declare @SQL nvarchar(max);
  declare @PARAMDEF nvarchar(255);

  select
    @FIRSTRESPONSEDATE = [FIRSTRESPONSEDATE],
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE]
  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTSEGMENTATIONACTIVE] on [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  --Only calculate the first response date if it hasn't been calculated yet, and the mailing is actually active...

  if @FIRSTRESPONSEDATE is null and @ACTIVE = 1
    begin
      begin try
        if  @CHANGEAGENTID is null
          exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        set @CURRENTDATE = getdate();
        set @PARAMDEF = '@RECORDSOURCEFIRSTRESPONSEDATE date output';

        --We know the mailing is active, so loop through all the distinct record sources...

        declare RECORDSOURCECURSOR cursor local fast_forward for
          select
            [NORMALGIFTIDSETREGISTERID]
          from dbo.[MKTSEGMENTATIONACTIVATE]
          where [SEGMENTATIONID] = @SEGMENTATIONID;

        open RECORDSOURCECURSOR;
        fetch next from RECORDSOURCECURSOR into @RECORDSOURCEGIFTIDSETID;

        while (@@fetch_status = 0)
        begin
            --Build the SQL statement for this record source

            set @SQL = 'select' + char(13) +
                        '  @RECORDSOURCEFIRSTRESPONSEDATE = min([GIFTIDSET].[DATE])' + char(13) +
                        'from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@RECORDSOURCEGIFTIDSETID) + ' as [GIFTIDSET]';

            --Execute the SQL to get the first response date for this record source

            exec sp_executesql @SQL, @PARAMDEF, @RECORDSOURCEFIRSTRESPONSEDATE = @RECORDSOURCEFIRSTRESPONSEDATE output;

            --Keep the earliest date for all record sources

            set @FIRSTRESPONSEDATE = case when @FIRSTRESPONSEDATE is null then @RECORDSOURCEFIRSTRESPONSEDATE when @RECORDSOURCEFIRSTRESPONSEDATE < @FIRSTRESPONSEDATE then @RECORDSOURCEFIRSTRESPONSEDATE else @FIRSTRESPONSEDATE end;

            fetch next from RECORDSOURCECURSOR into @RECORDSOURCEGIFTIDSETID;
        end

        close RECORDSOURCECURSOR;
        deallocate RECORDSOURCECURSOR;

        --Update the field in the table  

        update dbo.[MKTSEGMENTATIONACTIVE] set
          [FIRSTRESPONSEDATE] = @FIRSTRESPONSEDATE,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @SEGMENTATIONID;
      end try

      begin catch
        exec dbo.[USP_RAISE_ERROR];
        return 1;
      end catch
    end

  return 0;