This query relies on payment distributions. See Solution 107 for a query that ensures all payments are distributed.

This query totals up the distributions that are applied to pledges that are NOT tagged as Payroll Deductions. The query is also filtered to only include payment dates within a given range.


SELECT
e.Salutation,
n.DisplayName,
n.AddressLine1,
n.AddressLine2,
n.City,
n.State,
n.Zip,
d.TotalDistributed
FROM
camNamesV n,
camPeople e,
(SELECT
py.NameID,
SUM(di.DistributionAmount) AS TotalDistributed
FROM
camDistributions di,
camPayments py,
camPledges pl
WHERE
/* JOIN - to get payment date */
di.PaymentID = py.PaymentID
/* JOIN - to get to the pledge distributed to */
AND di.PledgeID = pl.PledgeID
/* FILTER - to limit to given dates */
AND py.PaymentDate >= '01-JAN-2004'
AND py.PaymentDate <= '31-DEC-2004'
/* FILTER - exclude Pledges tagged as Payroll Deduction */
AND pl.PledgeType <> 'P'
GROUP BY
py.NameID
HAVING
/* FILTER - limit to those who paid more than $250 */
SUM(di.DistributionAmount) >= 250
) d
WHERE
/* JOIN - to get the salutation if available */
n.NameID = e.NameID (+)
/* JOIN - to get the Total Amount Distributed */
AND n.NameID = d.NameID