UFN_MKTSEGMENTATIONSEGMENT_GETOFFERCOUNT

create function dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETOFFERCOUNT]
(
  @SEGMENTID uniqueidentifier,
  @TESTSEGMENTID uniqueidentifier,
  @TOTALOFFERS integer
)
returns integer
as
  begin
    declare @OFFERS integer = @TOTALOFFERS;

    declare @TESTSEGMENTOFFERS table (
      [ID] uniqueidentifier not null,
      [SEQUENCE] integer not null,
      [FRACTIONNUMERATOR] integer,
      [FRACTIONDENOMINATOR] integer,
      [OFFERS] integer not null
    );

    declare @ID uniqueidentifier;
    declare @TESTSAMPLESIZE integer;
    declare @TESTSAMPLESIZETYPECODE tinyint;
    declare @TESTSEQUENCE integer;
    declare @FRACTION nvarchar(10);
    declare @SLASH integer;
    declare @FRACTIONNUMERATOR integer;
    declare @FRACTIONDENOMINATOR integer;
    declare @SAMPLESIZEOFFER integer;
    declare @ALLTESTSEGMENTSUSEPERCENT bit = 1;
    declare @ALLTESTSEGMENTSUSEFRACTION bit = 1;

    declare TESTSEGMENTCURSOR cursor local fast_forward for
      select
        [ID],
        [SAMPLESIZE],
        [SAMPLESIZETYPECODE],
        [SEQUENCE],
        [FRACTION]
      from dbo.[MKTSEGMENTATIONTESTSEGMENT]
      where [SEGMENTID] = @SEGMENTID
      order by [SEQUENCE];

    open TESTSEGMENTCURSOR;
    fetch next from TESTSEGMENTCURSOR into @ID, @TESTSAMPLESIZE, @TESTSAMPLESIZETYPECODE, @TESTSEQUENCE, @FRACTION;

    while (@@FETCH_STATUS = 0)
      begin
        if @TESTSAMPLESIZETYPECODE <> 0 -- percent

          set @ALLTESTSEGMENTSUSEPERCENT = 0;

        if @TESTSAMPLESIZETYPECODE = 2 -- fraction

          begin
            set @SLASH = charindex('/', @FRACTION);

            if @SLASH > 0
              begin
                set @FRACTIONNUMERATOR = cast(substring(@FRACTION, 1, @SLASH - 1) as integer);
                set @FRACTIONDENOMINATOR = cast(substring(@FRACTION, @SLASH + 1, 10) as integer);
              end
            else
              begin
                -- invalid segment sample size fraction, but we can't force raise an error here, so just zero it out and let it get a divide-by-zero error below

                set @FRACTIONNUMERATOR = 0;
                set @FRACTIONDENOMINATOR = 0
              end
          end
        else
          begin
            set @ALLTESTSEGMENTSUSEFRACTION = 0;
            set @FRACTIONNUMERATOR = null;
            set @FRACTIONDENOMINATOR = null;
          end

        set @SAMPLESIZEOFFER = (case @TESTSAMPLESIZETYPECODE
                                when 0 then -- percent 

                                  (@TOTALOFFERS * (cast(@TESTSAMPLESIZE as decimal(5,2)) / 100))
                                when 1 then -- records 

                                  (case when @TESTSAMPLESIZE < @OFFERS then @TESTSAMPLESIZE else @OFFERS end)
                                when 2 then -- fraction

                                  floor(@TOTALOFFERS * (@FRACTIONNUMERATOR / cast(@FRACTIONDENOMINATOR as numeric(30, 20))))
                                end);

        -- the offers can only be the minimum number of records left after taking out all test segments above this one

        set @SAMPLESIZEOFFER = (case when @SAMPLESIZEOFFER < @OFFERS then @SAMPLESIZEOFFER else @OFFERS end);
        set @OFFERS -= @SAMPLESIZEOFFER;

        insert into @TESTSEGMENTOFFERS (
          [ID],
          [SEQUENCE],
          [FRACTIONNUMERATOR],
          [FRACTIONDENOMINATOR],
          [OFFERS]
        ) values (
          @ID,
          @TESTSEQUENCE,
          @FRACTIONNUMERATOR,
          @FRACTIONDENOMINATOR,
          @SAMPLESIZEOFFER
        );

        fetch next from TESTSEGMENTCURSOR into @ID, @TESTSAMPLESIZE, @TESTSAMPLESIZETYPECODE, @TESTSEQUENCE, @FRACTION;
      end

    close TESTSEGMENTCURSOR;
    deallocate TESTSEGMENTCURSOR;

    if exists (select top 1 1 from @TESTSEGMENTOFFERS)
      begin
        declare @NEEDTODISTRIBUTE bit = 0;

        if @ALLTESTSEGMENTSUSEPERCENT = 1
     begin
            -- add up all the percentages to see if they add up to 100%

            if (select sum([SAMPLESIZE]) from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = @SEGMENTID) = 100
              set @NEEDTODISTRIBUTE = 1;
          end

        else if @ALLTESTSEGMENTSUSEFRACTION = 1
          begin
            -- add up all the fractions to see if they add up to 1

            declare @RESULTNUMERATOR bigint = 0;
            declare @COMMONDENOMINATOR bigint = 1;

            declare FRACTIONADDCURSOR cursor local fast_forward for
              select [FRACTIONNUMERATOR], [FRACTIONDENOMINATOR]
              from @TESTSEGMENTOFFERS;

            open FRACTIONADDCURSOR;
            fetch next from FRACTIONADDCURSOR into @FRACTIONNUMERATOR, @FRACTIONDENOMINATOR;

            while (@@FETCH_STATUS = 0)
              begin
                set @RESULTNUMERATOR = (@RESULTNUMERATOR * @FRACTIONDENOMINATOR) + (@FRACTIONNUMERATOR * @COMMONDENOMINATOR);
                set @COMMONDENOMINATOR = @COMMONDENOMINATOR * @FRACTIONDENOMINATOR;

                fetch next from FRACTIONADDCURSOR into @FRACTIONNUMERATOR, @FRACTIONDENOMINATOR;
              end

            close FRACTIONADDCURSOR;
            deallocate FRACTIONADDCURSOR;

            -- check if the fractions add up to 1

            if @RESULTNUMERATOR = @COMMONDENOMINATOR
              set @NEEDTODISTRIBUTE = 1;
          end

        -- now distribute the remaining records across the test segments in reverse order

        if @NEEDTODISTRIBUTE = 1
          begin
            declare @REMAININGOFFERS integer = (select @TOTALOFFERS - sum([OFFERS]) from @TESTSEGMENTOFFERS);

            declare DISTRIBUTECURSOR cursor local fast_forward for
              select [ID], [OFFERS]
              from @TESTSEGMENTOFFERS
              order by [SEQUENCE] desc;

            open DISTRIBUTECURSOR;
            fetch next from DISTRIBUTECURSOR into @ID, @OFFERS;

            while (@@FETCH_STATUS = 0 and @REMAININGOFFERS > 0)
              begin
                update @TESTSEGMENTOFFERS set
                  [OFFERS] = [OFFERS] + 1
                where [ID] = @ID;

                set @REMAININGOFFERS -= 1;

                fetch next from DISTRIBUTECURSOR into @ID, @OFFERS;  
              end

            close DISTRIBUTECURSOR;
            deallocate DISTRIBUTECURSOR;
          end
      end

    if @TESTSEGMENTID is null
      -- get the offers left over for the parent segment

      select
        @OFFERS = (@TOTALOFFERS - isnull(sum([OFFERS]),0))
      from @TESTSEGMENTOFFERS;
    else
      -- get the offers for the individual test segment

      select
        @OFFERS = [OFFERS]
      from @TESTSEGMENTOFFERS
      where [ID] = @TESTSEGMENTID;  

    return @OFFERS;
  end