I need some SQL help! We seem to always be doing a pledge drive at some sort at least one of our campuses, and I've been requested to provide a report that breaks down pledged giving.  Here's an example (I'm just using Dynamic Data View blocks for these): 

I'm having 3 problems:

1. Pledger Detail: I'm missing some dollars here - if you add up all of the values in the Pledged column, it is lower than the Total Pledged amount.  Same with Total Given.  Here's part of the problem - if someone has pledged but hasn't given anything, the are not appearing at all.  Here's the SQL I'm using for the Pledger Detail:

SELECT p.Id, pa.Id as AliasId, (p.NickName + ' ' + p.LastName) as Name, fa.Name as Drive, fp.TotalAmount as Pledged, SUM(Amount) as Given, fp.TotalAmount - SUM(Amount) as Remaining
        FROM FinancialTransactionDetail ftd 
        JOIN FinancialTransaction ft ON ftd.TransactionId = ft.Id
        JOIN FinancialPledge fp ON ft.AuthorizedPersonAliasId = fp.PersonAliasId
        JOIN FinancialAccount fa ON ftd.AccountId = fa.Id 
        JOIN PersonAlias pa ON ft.AuthorizedPersonAliasId = pa.Id 
        JOIN Person p ON pa.AliasPersonId = p.Id 
    WHERE ftd.AccountId = fp.AccountId  AND fa.Id = 231
    GROUP BY p.Id, pa.Id, p.NickName, p.LastName, fa.Name, fp.TotalAmount
    ORDER BY p.LastName

I think the join types may be the problem, but if I change everything to Right Joins, I get the exact same results.  When someon has a pledge but has no giving towards the fund they pledged to, I don't get those values. How can I 'fix' this code to include NULL values in my joins? (I think that's where the problem is!)

2. The totals on the right side are correct, except for the Given With Pledge and Given Without Pledge rows.  Those two amounts add up to Total Given, but they are off - given with pledge should be around $200,000.00 higher I think (and without $200,000 lower of course).  Here's the SQL for that:

SELECT 'Total Pledged' as 'Total', SUM(fp.TotalAmount) as 'Amount' FROM
    FinancialPledge fp
    WHERE fp.AccountId = 231

UNION

SELECT 'Total Given' as 'Total', SUM(ftd.Amount) as 'Amount' FROM
    FinancialTransactionDetail ftd
    WHERE ftd.AccountId = 231

UNION

SELECT 'Given With Pledge' as 'Total', (SELECT SUM(Amount) as 'Given With Pledge'
        FROM FinancialTransactionDetail ftd 
        JOIN FinancialTransaction ft ON ftd.TransactionId = ft.Id
        JOIN FinancialPledge fp ON ft.AuthorizedPersonAliasId = fp.PersonAliasId
        JOIN FinancialAccount fa ON ftd.AccountId = fa.Id 
        JOIN PersonAlias pa ON ft.AuthorizedPersonAliasId = pa.Id 
        JOIN Person p ON pa.AliasPersonId = p.Id 
    WHERE ftd.AccountId = fp.AccountId AND fa.Id = 231
    GROUP BY fa.Name)

UNION

    SELECT 'Given Without Pledge' as 'Total', ((SELECT SUM(ftd.Amount) as 'Amount2' FROM
        FinancialTransactionDetail ftd
        WHERE ftd.AccountId = 231) - (SELECT SUM(Amount) as 'Given With Pledge'
        FROM FinancialTransactionDetail ftd 
        JOIN FinancialTransaction ft ON ftd.TransactionId = ft.Id
        JOIN FinancialPledge fp ON ft.AuthorizedPersonAliasId = fp.PersonAliasId
        JOIN FinancialAccount fa ON ftd.AccountId = fa.Id 
        JOIN PersonAlias pa ON ft.AuthorizedPersonAliasId = pa.Id 
        JOIN Person p ON pa.AliasPersonId = p.Id 
    WHERE ftd.AccountId = fp.AccountId AND fa.Id = 231
    GROUP BY fa.Name ))

UNION 

SELECT 'Remaining' as 'Total', ((SELECT SUM(fp.TotalAmount) as 'Amount' FROM
    FinancialPledge fp WHERE fp.AccountId = 231) - (SELECT SUM(ftd.Amount) as 'Amount2' FROM
    FinancialTransactionDetail ftd
    WHERE ftd.AccountId = 231))

Again, I think the Given with Pledge and Given Without Pledge are off because of my Joins.

3.  Along the same lines, I'm trying to show this info on an individual's Profile: 

(Again, just a Dynamic Data block using a Stored Procedure)   This is working great, except for when someone has made a pledge but hasn't yet given towards it.  In that case, that pledge won't show up at all.  Here's the SQL:

SELECT p.Id, pa.Id as AliasId, p.FirstName, p.LastName, fa.Name, fp.TotalAmount as Pledged, SUM(Amount) as Given, fp.TotalAmount - SUM(Amount) as Remaining
        FROM FinancialTransactionDetail ftd 
        JOIN FinancialTransaction ft ON ftd.TransactionId = ft.Id
        JOIN FinancialPledge fp ON ft.AuthorizedPersonAliasId = fp.PersonAliasId
        JOIN FinancialAccount fa ON ftd.AccountId = fa.Id 
        JOIN PersonAlias pa ON ft.AuthorizedPersonAliasId = pa.Id 
        JOIN Person p ON pa.AliasPersonId = p.Id 
    WHERE ftd.AccountId = fp.AccountId AND fa.AccountTypeValueId = 577 AND p.Id = @PersonId
    GROUP BY p.Id, pa.Id, p.FirstName, p.LastName, fa.Name, fp.TotalAmount
    ORDER BY p.LastName

Again, I think the trouble is with my Joins.

I'm not sure where to go from here - if anyone has any insight, I'd greatly appriciate it. Thanks!