NEED A REPORT ASAP! :)

[Stephanie Payne]

I am looking for a detailed pledge report and cannot seem to do it no matter what filters/reports I run. I attached an example, a csv file of exactly what I am looking for in one report. If anyone can help, I would really appreciate it!! Thanks!! 

[Robert Jones]

This is the majority of what you want. Most of it stored in one table, if the FCR1331 would let you just select all pledges at once this would be similar.

SELECT c.FirstName, c.LastName, a.AddressName, co.Description, p.Amount, p.Received, COUNT(con.contributionid) AS 'Count', p.Interval, p.StartDate, p.EndDate

FROM tblPledges p

LEFT JOIN tblAddresses a ON p.AddressID = a.AddressID

LEFT JOIN tblContacts c ON p.ContactID = c.ContactID

INNER JOIN tblCodes co ON p.Fund = co.CodeID

INNER JOIN tblContributions con ON p.Fund = con.DesignatedFund AND (p.ContactID = con.ContactID OR p.AddressID = con.AddressID)

GROUP BY c.FirstName, c.LastName, a.AddressName, co.Description, p.Amount, p.Received, p.Interval, p.StartDate, p.EndDate

Some Notes:

Campus isn't included just due to the way tables interact, it'd be a hassle to get. Hopefully, that isn't that important.

The Interval appears to be in days. 0 is Other, 7 is Weekly, 30 is Monthly. That's all I had in my DB, so not sure the others, but following that I'd imagine yearly would be 365.

Number of gifts isn't 100% accurate. If someone has multiple pledges created for the same campaign, then it counts all donations to that same fund for each one. Example: There is a yearly building fund pledge created, but uses the exact same fund code "building fund" each time. Each of those building fund pledges will have the same number of gifts, because. It's something that could be fixed, but would take a while to get it working right. As long as each pledge fund  code is unique this won't even be a problem.