UFN_MKTSEGMENTATIONSEGMENTLIST_GETQUANTITY

CREATE function dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETQUANTITY]
(
  @SEGMENTATIONSEGMENTID uniqueidentifier,
  @RECEIVEDVIACODE tinyint = 3 -- both

)
returns integer
as
begin
  declare @RENTALQUANTITY integer;
  declare @EXCHANGEQUANTITY integer;
  declare @QUANTITY integer;
  declare @ACTIVE bit;
  declare @SAMPLESIZE integer;
  declare @SAMPLESIZETYPECODE tinyint;
  declare @ISVENDORMANAGED bit;

  set @RENTALQUANTITY = 0;
  set @EXCHANGEQUANTITY = 0;

  select
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
    @SAMPLESIZE = [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE],
    @SAMPLESIZETYPECODE = [MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE],
    @ISVENDORMANAGED = dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENTATIONSEGMENT].[SEGMENTID])
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;

  if @ACTIVE = 0
    --For non-activated mailings...

    select
      @RENTALQUANTITY = (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY] else [MKTSEGMENTLIST].[RENTALQUANTITY] end),
      @EXCHANGEQUANTITY = (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY] else [MKTSEGMENTLIST].[EXCHANGEQUANTITY] end)
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;
  else
    --For activated mailings...

    select
      @RENTALQUANTITY = [MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY],
      @EXCHANGEQUANTITY = [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;

  set @QUANTITY = (case @RECEIVEDVIACODE
                    when 3 then @RENTALQUANTITY + @EXCHANGEQUANTITY 
                    when 1 then @RENTALQUANTITY
                    when 2 then @EXCHANGEQUANTITY
                   end);

  if @ISVENDORMANAGED = 0  --Only imported lists will have a sample size

    begin
      if @SAMPLESIZETYPECODE = 0  --Percent

        begin
          set @QUANTITY = floor(cast(@QUANTITY as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100));
        end
      else  --Records

        begin
          if @QUANTITY > @SAMPLESIZE
            set @QUANTITY = @SAMPLESIZE;
        end
    end

  return @QUANTITY;
end