UFN_MKTSEGMENTATION_GETTOTALLISTSEGMENTCOST

CREATE function dbo.[UFN_MKTSEGMENTATION_GETTOTALLISTSEGMENTCOST]
(
  @SEGMENTATIONID uniqueidentifier,
  @SEGMENTID uniqueidentifier = null
)
returns money
as
begin
  declare @TOTALLISTCOST money;

  set @TOTALLISTCOST = 0;

  -- COSTBASIS: 1 = per thousand, 2 = flat


  -- this is an ugly sql statement, but this way avoids rounding errors that result 

  -- from summing UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD


  -- it looks horrendous, but all it does is, for each list segment:

  --   calculate the total (base + adjustment) rental cost, if the rental quantity is > 0,

  --   calculate the total (base + adjustment) exchange cost, if the exchange quantity is > 0, 

  --   add those together

  -- this is summed for the mailing


  if dbo.[UFN_MKTSEGMENTATION_ISACTIVE](@SEGMENTATIONID) = 0
    --For non-activated mailings...

    select @TOTALLISTCOST = 
      isnull(sum(
        --Rental costs

        (case when (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY] else [MKTSEGMENTLIST].[RENTALQUANTITY] end) > 0 then
          --Base rental cost

          (case when (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOSTBASISCODE] else [MKTLIST].[BASERENTALCOSTBASISCODE] end) = 1 then  --Per thousand

            --Base rental cost times rental quantity divided by 1000

            (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOST] else [MKTLIST].[BASERENTALCOST] end) *
            ((case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY] else [MKTSEGMENTLIST].[RENTALQUANTITY] end) / cast(1000 as decimal(20,5)))
          else
            --Base rental cost basis code is flat

            (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOST] else [MKTLIST].[BASERENTALCOST] end)
          end)
          +
          --Rental cost adjustment

          (case when (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENTBASISCODE] else [MKTSEGMENTLIST].[RENTALCOSTBASISCODE] end) = 1 then  --Per thousand

            --Rental cost adjustment times rental quantity divided by 1000

            (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENT] else [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT] end) *
            ((case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY] else [MKTSEGMENTLIST].[RENTALQUANTITY] end) / cast(1000 as decimal(20,5)))
          else
            --Rental cost adjustment basis code is flat

            (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENT] else [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT] end)
          end)
        else 0 end)
        +
        --Exchange costs

        (case when (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY] else [MKTSEGMENTLIST].[EXCHANGEQUANTITY] end) > 0 then
          --Base exchange cost

          (case when (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOSTBASISCODE] else [MKTLIST].[BASEEXCHANGECOSTBASISCODE] end) = 1 then  --Per thousand

            --Base exchange cost times exchange quantity divided by 1000

            (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOST] else [MKTLIST].[BASEEXCHANGECOST] end) *
            ((case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY] else [MKTSEGMENTLIST].[EXCHANGEQUANTITY] end) / cast(1000 as decimal(20,5)))
          else
            --Base exchange cost basis code is flat

            (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOST] else [MKTLIST].[BASEEXCHANGECOST] end)
          end)
          +
          --Exchange cost adjustment

          (case when (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENTBASISCODE] else [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE] end) = 1 then  --Per thousand

            --Exchange cost adjustment times exchange quantity divided by 1000

            (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] else [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] end) *
            ((case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY] else [MKTSEGMENTLIST].[EXCHANGEQUANTITY] end) / cast(1000 as decimal(20,5)))
          else
            --Exchange cost adjustment basis code is flat

            (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] else [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] end)
          end)
        else 0 end)
      ), 0)
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
    left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    and (@SEGMENTID is null or [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID);
  else
    select @TOTALLISTCOST =
      isnull(sum(
        --Rental costs

        (case when [MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY] > 0 then
          --Base rental cost

          (case when [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOSTBASISCODE] = 1 then  --Per thousand

            [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOST] * ([MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY] / cast(1000 as decimal(20,5)))
          else
            [MKTSEGMENTATIONSEGMENTLIST].[BASERENTALCOST]
          end)
          +
          --Rental cost adjustment

          (case when [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENTBASISCODE] = 1 then  --Per thousand

            [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENT] * ([MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY] / cast(1000 as decimal(20,5)))
          else
            [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENT]
          end)
        else 0 end)
        +
        --Exchange costs

        (case when [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY] > 0 then
          --Base exchange cost

          (case when [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOSTBASISCODE] = 1 then  --Per thousand

            [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOST] * ([MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY] / cast(1000 as decimal(20,5)))
          else
            [MKTSEGMENTATIONSEGMENTLIST].[BASEEXCHANGECOST]
          end)
          +
          --Exchange cost adjustment

          (case when [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENTBASISCODE] = 1 then  --Per thousand

            [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] * ([MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY] / cast(1000 as decimal(20,5)))
          else
            [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENT]
          end)
        else 0 end)
      ), 0)
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    and (@SEGMENTID is null or [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID);

  return @TOTALLISTCOST;
end