This query can be broken down into several parts. First, PledgesByYear totals the amount pledged by people by year:
select Y.YearDesc, Pe.NameID, SUM(Pl.PledgeAmount) as Pledged
from camPeople Pe, camPledges Pl, camAppeals A, camYears Y
where Pe.NameID = Pl.NameID (+)
and NVL(Pl.AppealID,-1) = A.AppealID (+)
and NVL(A.YearID,-1) = Y.YearID (+)
group by Y.YearDesc, Pe.NameID
Next, SpouseRelations gets a list of all people with a Spouse:
select R.NameID, R.RelatedNameID
from camRelationships R, camRelationTypes T
where R.RelationTypeID = T.RelationTypeID
and T.ParentDesc = 'Spouse'
Next, DonorsWithSpouse gets a list of Donors, their first and last names, their spouses, and their spouses' names:
select Donors.NameID DonorNameID, Donors.FirstName DonorFirstName, Donors.LastName DonorLastName,
Spouses.NameID SpouseNameID, Spouses.FirstName SpouseFirstName, Spouses.LastName SpouseLastName
from camPeople Donors, camPeople Spouses,
SpouseRelations, SpouseRelations DuplicateRelations
where
Donors.NameID = SpouseRelations.NameID (+) and
NVL(SpouseRelations.RelatedNameID, -1) = Spouses.NameID (+) and
Donors.NameID = DuplicateRelations.RelatedNameID (+) and
DuplicateRelations.RelatedNameID is null
Next, PledgesForBoth gets all the pledges made by either the donor or their spouse:
select P.YearDesc, NVL(P.Pledged, 0) DonorPledged, 0 as SpousePledged, D.*
from DonorsWithSpouse D, PledgesByYear P
where D.DonorNameID = P.NameID (+)
union
select P.YearDesc, 0 as DonorPledged, NVL(P.Pledged, 0) SpousePledged, D.*
from DonorsWithSpouse D, PledgesByYear P
where D.SpouseNameID = P.NameID (+)
Next, PledgesSummary combines the pledges so that the donor and spouse totals are combined into a single row:
select
YearDesc,
DonorNameID, DonorFirstName, DonorLastName, SUM(DonorPledged) DonorPledged,
SpouseNameID, SpouseFirstName, SpouseLastName, SUM(SpousePledged) SpousePledged,
SUM(DonorPledged) + SUM(SpousePledged) as TotalPledged
from
PledgesForBoth
group by
YearDesc,
DonorNameID, DonorFirstName, DonorLastName,
SpouseNameID, SpouseFirstName, SpouseLastName
Finally, everything is combined with the address and email. In this example, the list is also filtered to only include years on or after 2005 and only those donors who gave a combined gift of more than $1000:
select * from (
with PledgesByYear as
(select Y.YearDesc, Pe.NameID, SUM(Pl.PledgeAmount) as Pledged
from camPeople Pe, camPledges Pl, camAppeals A, camYears Y
where Pe.NameID = Pl.NameID (+)
and NVL(Pl.AppealID,-1) = A.AppealID (+)
and NVL(A.YearID,-1) = Y.YearID (+)
group by Y.YearDesc, Pe.NameID),
SpouseRelations as
(select R.NameID, R.RelatedNameID
from camRelationships R, camRelationTypes T
where R.RelationTypeID = T.RelationTypeID
and T.ParentDesc = 'Spouse'),
DonorsWithSpouse as
(select Donors.NameID DonorNameID, Donors.FirstName DonorFirstName, Donors.LastName DonorLastName,
Spouses.NameID SpouseNameID, Spouses.FirstName SpouseFirstName, Spouses.LastName SpouseLastName
from camPeople Donors, camPeople Spouses,
SpouseRelations, SpouseRelations DuplicateRelations
where
Donors.NameID = SpouseRelations.NameID (+) and
NVL(SpouseRelations.RelatedNameID, -1) = Spouses.NameID (+) and
Donors.NameID = DuplicateRelations.RelatedNameID (+) and
DuplicateRelations.RelatedNameID is null),
PledgesForBoth as
(select P.YearDesc, NVL(P.Pledged, 0) DonorPledged, 0 as SpousePledged, D.*
from DonorsWithSpouse D, PledgesByYear P
where D.DonorNameID = P.NameID (+)
union
select P.YearDesc, 0 as DonorPledged, NVL(P.Pledged, 0) SpousePledged, D.*
from DonorsWithSpouse D, PledgesByYear P
where D.SpouseNameID = P.NameID (+)),
PledgesSummary as
(select
YearDesc,
DonorNameID, DonorFirstName, DonorLastName, SUM(DonorPledged) DonorPledged,
SpouseNameID, SpouseFirstName, SpouseLastName, SUM(SpousePledged) SpousePledged,
SUM(DonorPledged) + SUM(SpousePledged) as TotalPledged
from
PledgesForBoth
group by
YearDesc,
DonorNameID, DonorFirstName, DonorLastName,
SpouseNameID, SpouseFirstName, SpouseLastName)
select
P.*,
E.EmailAddress,
N.DisplayName,
N.AddressLine1,
N.AddressLine2,
N.City,
N.State,
N.Zip
from
PledgesSummary P,
camNamesV N,
camEmails E
where
P.DonorNameID = N.NameID and
P.DonorNameID = E.NameID (+) and
E.PrimaryEmail (+) = 'Y' and
P.YearDesc >= '2005' and
P.TotalPledged >= 1000
)