USP_DATALIST_MARKETINGEFFORTPERFORMANCEREVIEWPACES

create procedure BBDW.USP_DATALIST_MARKETINGEFFORTPERFORMANCEREVIEWPACES
(
    @ID uniqueidentifier,
  @COMPAREDEFFORTS xml = null
)
as
    set nocount on;

  --Pace vars

  DECLARE @TOTALQUANTITY decimal(19,4) = null
  DECLARE @TOTALCOST money = null
  DECLARE @GROSSREVENUEPACE money = null
  DECLARE @TOTALRESPONSESPACE decimal (19,0) = null

  --Calculate the paces

    SELECT 
    /* PACE = CURRENT EFFORT CURRENT DAY * AVG(SUM((COMPARE 120 DAY) / (COMPARE CURRENT DAY))) */ 
    @GROSSREVENUEPACE = 
        /* GrossRevenue */
        COALESCE(MAX(ms.SEGMENTATIONTOTALGIFTAMOUNT) *
                    AVG(CONVERT(DECIMAL, msCompare.SEGMENTATIONTOTALGIFTAMOUNT)/
                          CONVERT(DECIMAL, NULLIF(msdr.SEGMENTATIONCUMULATIVEREVENUE,0))),
    0),
    @TOTALRESPONSESPACE = 
        /* TotalResponses */
        COALESCE(MAX(ms.SEGMENTATIONRESPONSES) *    
                    AVG(CONVERT(DECIMAL, msCompare.SEGMENTATIONRESPONSES)/
                          CONVERT(DECIMAL, NULLIF(msdr.SEGMENTATIONCUMULATIVERESPONSES,0))),
    0),
        @TOTALQUANTITY = MAX(ms.SEGMENTATIONQUANTITY),
        @TOTALCOST = MAX(ms.SEGMENTATIONTOTALCOST)
    FROM @COMPAREDEFFORTS.nodes('/COMPAREDEFFORTS/ITEM') T(c)
        INNER JOIN BBDW.DIM_MARKETINGSEGMENTATION ms
        ON ms.SEGMENTATIONSYSTEMID = @ID

        /* COMPARE EFFORTS */
        INNER JOIN BBDW.DIM_MARKETINGSEGMENTATION msCompare
        ON msCompare.SEGMENTATIONSYSTEMID = T.c.value('(COMPARETOSEGMENTATIONID)[1]','uniqueidentifier')

        /* COMPARE EFFORTS */
        LEFT JOIN BBDW.FACT_MARKETINGSEGMENTATIONDAILYRESPONSE msdr
        ON msdr.MARKETINGSEGMENTATIONDIMID = msCompare.MARKETINGSEGMENTATIONDIMID
        AND msdr.SEGMENTATIONDAYSSINCEFIRSTRESPONSE = DATEDIFF(DAY, ms.SEGMENTATIONFIRSTRESPONSEDATE, GETDATE());

    SELECT 
    /* PACE = CURRENT EFFORT CURRENT DAY * ((COMPARE 120 DAY) / (COMPARE CURRENT DAY)) */ 
    /* CostPerDollarRaisedPace = TotalCost/GrossRevenue */
        CONVERT(DECIMAL, ISNULL(@TOTALCOST/NULLIF(@GROSSREVENUEPACE,0),0)),
    /* CostToAcquirePace = TotalCost/Responses */
      CONVERT(DECIMAL, ISNULL(@TOTALCOST/NULLIF(@TOTALRESPONSESPACE,0),0)),
      /* NetRevenuePace = (GrossRevenue - TotalCost) */
      CONVERT(DECIMAL, ISNULL(@GROSSREVENUEPACE - @TOTALCOST,0)),
    @GROSSREVENUEPACE,
      /* AverageGiftAmountPace = GrossRevenue/Responses */
    CONVERT(DECIMAL, ISNULL(@GROSSREVENUEPACE/NULLIF(@TOTALRESPONSESPACE,0),0)),
    @TOTALRESPONSESPACE,
      /* ResponseRatePace = Responses/Quantity*100   Note:Qty and "100" cancel out */
      CONVERT(INTEGER, ISNULL(@TOTALRESPONSESPACE/NULLIF(@TOTALQUANTITY,0),0)*100)

    return 0;