gistfile1.txt

DECLARE FundingId INT
@FundraiserID = 12345

SELECT    F.FundraiserName AS CharityName 
        ,FRS.FundraiserRevenueStreamName AS AppealName 
        ,CASE  
            WHEN EC.EventContributionId IS NULL THEN 'Direct Donations'  
            ELSE 'Fundraising Pages'  
        END AS DonationCategory 
        ,CASE D.DonationSourceId  
            WHEN 10 THEN 'SMS'  
            ELSE 'Not SMS'   
        END AS DonationType 
        ,CASE  
            WHEN D.ConsumerCreditCardMandateId > 0 THEN 'Recurring'  
            WHEN M.DirectDebitMandateId IS NOT NULL THEN 'Recurring'  
            ELSE 'Not Recurring'   
        END AS DonationRecursion 
        ,SUM(ISNULL(L.Amount, 0)) AS DonationAmount 
        ,SUM(ISNULL(L.EstimatedTaxReclaim, 0)) AS GiftAidAmount 
        ,SUM(ISNULL(L.Amount, 0) + ISNULL(L.EstimatedTaxReclaim, 0)) AS TotalAmount 
        ,COUNT(*) AS DonationCount 
INTO #tmp 
FROM DB.dbo.DB_Donation D with (nolock) 
    JOIN DB.dbo.DB_DonationLine L  with (nolock) 
    ON  D.DonationId = L.DonationId 
    JOIN DB.dbo.DB_FundraiserRevenueStream FRS  with (nolock) 
    ON  L.FundraiserRevenueStreamId = FRS.FundraiserRevenueStreamId 
    JOIN DB.dbo.DB_Fundraiser F  with (nolock) 
    ON FRS.FundraiserId = F.FundraiserId 
    JOIN DB.dbo.DB_ProcessStatus P  with (nolock) 
    ON  D.ProcessStatusId = P.ProcessStatusId AND P.ProcessStatusCategory = 'Valid' 
    LEFT OUTER JOIN DB.dbo.DB_EventContribution EC  with (nolock) 
    ON  L.DonationLineId = EC.DonationLineId 
    LEFT OUTER JOIN DB.dbo.DB_DonationDirectDebitMandateLink M  with (nolock) 
    ON D.DonationId = M.DonationId 
WHERE F.FundraiserId = @FundraiserId 
GROUP BY F.FundraiserName 
        ,FRS.FundraiserRevenueStreamName 
        ,CASE  
            WHEN EC.EventContributionId IS NULL THEN 'Direct Donations'  
            ELSE 'Fundraising Pages'  
        END 
        ,CASE D.DonationSourceId  
            WHEN 10 THEN 'SMS'  
            ELSE 'Not SMS'   
        END 
        ,CASE  
            WHEN D.ConsumerCreditCardMandateId > 0 THEN 'Recurring'  
            WHEN M.DirectDebitMandateId IS NOT NULL THEN 'Recurring'  
            ELSE 'Not Recurring'   
        END 
 
/* 
** Pull results from table and provide totals 
*/ 
SELECT     T.CharityName 
        ,T.AppealName 
        ,T.DonationCategory 
        ,T.DonationType 
        ,T.DonationRecursion 
        ,T.DonationAmount 
        ,T.GiftAidAmount 
        ,T.TotalAmount 
        ,T.DonationCount 
FROM #tmp T 
UNION ALL 
SELECT     'Total' AS CharityName 
        ,'' AS AppealName 
        ,'' AS DonationCategory 
        ,'' AS DonationType 
        ,'' AS DonationRecursion 
        ,SUM(T.DonationAmount) AS DonationAmount 
        ,SUM(T.GiftAidAmount) AS GiftAidAmount 
        ,SUM(T.TotalAmount) AS TotalAmount 
        ,SUM(T.DonationCount) AS DonationCount 
FROM #tmp T 
ORDER BY T.CharityName 
        ,T.AppealName 
        ,T.DonationCategory 
        ,T.DonationType 
        ,T.DonationRecursion