A Query To Show How Much Has Been Pledged and How Much Has Come In

I'm thinking there has to be a simple solution I'm not seeing.


Here's the story: My predecessor created a query to track pledges for an event in RE.  How much did people pledge, how much do they owe.  


The data that's being pulled up is sort of a hot mess, though.  The report pulls up who has pledged, and how much they've pledged. It shows the balance of the pledge.  However what's throwing me off is that if you made a payment towards the pledge that information can be found in the gift amount along with the pledge amount.  In order to get a total of what was pledged, I have to pay attention to the gift type and manually remove that from the query after it's exported into Excel.


I'm thinking there has to be a more eloquent way to do the query to show: This is the grand total we got pledged for the event.  This is the grand total of what hasn't been paid.  And then broken down by donor as to what they pledged vs. what they paid.  And creating a nice report.where pledge amount and paid amount aren't in the same column but broken out into separate columns with another column showing what's left.


Or, am I just thinking wishfully?


Here's the criteria: 

Gift GL Post Date = This calendar yr

AND gift type = Pledge

AND fund id = name of fund

OR appeal ID one of this appeal or that appeal


Here's the output:

Constituent ID

Name

Gift ID

Gift Type

Gift GL Post Date

Gift Amount

Gift Pledge Balance

Campaign ID

Fund ID

Appeal ID