spGetContentComparisonDonationSummary

  CREATE PROCEDURE [dbo].[spGetContentComparisonDonationSummary]
  (
  @CCPID int
  )
  AS

  SELECT
  sc.Title,
  Count(dt.GiftAmount) AS NumGifts,
  Avg(dt.GiftAmount) AS AverageGiftAmount,
  Sum(dt.GiftAmount) AS TotalGiftAmount,
  cc.Conversions,
  cc.ID AS CCPRelID,
  Sum(CASE
  WHEN CAST(REPLACE(CAST(dt.xmlobjectdata AS nvarchar(max)), 'encoding="utf-8"',    '') AS XML).value(
  'declare namespace ns="urn:blackbaud.RE7.XDATA"; (/ns:ShelbyDonationTran/ns:Gift/ns:Recurrence)[1]',
  'nvarchar'
  ) IS NULL THEN 0
  ELSE 1
  END) AS Recurring
  FROM
  contentcomparisonpart ccp
  INNER JOIN contentcomparisons cc ON cc.CCPID = ccp.ID
  INNER JOIN sitecontent sc ON cc.cid = sc.id
  LEFT OUTER JOIN contentcomparisondonationtransactions ccdt ON cc.id = ccdt.CCPRelID
  INNER JOIN donationtransactions dt ON dt.DonationTransactionsID = ccdt.DonationTransactionID
  WHERE
  ccp.ID = @CCPID
  GROUP BY
  sc.Title,
  cc.ID,
  cc.Conversions