Used for leadership acknowledgements. This query will pull the name, address, amount pledged by donor and spouse. The list is then filtered to those that gave at least $500 (combined).

Before using this query, you must know the following values to update the query:
1. Year Description

To get a list of year descriptions, the query is:
select yeardesc from camyears

2. Relationship Type ID for Spouse relationship

To get this value, the query is:
select relationtypeid
from camrelationtypes
where parentdesc = 'Spouse'

3. And finally, the query for the donor/spouse combined giving is as follows. Be sure to use the correct values from the previous two queries.

SELECT
Peo.NameID
,Peo.FirstName
,Peo.LastName
,NVL(Peo.Salutation, Peo.FirstName || DECODE(SPeo.FirstName, NULL, '', ' & ' || SPeo.FirstName)) as Salutation
,Nam.DisplayName
,Nam.AddressLine1
,Nam.AddressLine2
,Nam.City
,Nam.State
,Nam.Zip
,NVL(PeopleGift.Pledged,0) AS Pledged
,SpousalRelationships.NameID AS SpouseNameID
,SPeo.FirstName AS SpouseFirstName
,SPeo.LastName AS SpouseLastName
,SPeo.Salutation AS SpouseSalutation
,SNam.DisplayName AS SpouseDisplayName
,NVL(SpousesGift.Pledged,0) AS SpousePledged
,NVL(PeopleGift.Pledged,0) + NVL(SpousesGift.Pledged,0) AS TotalPledged

FROM
camPeople Peo,
(
SELECT Peo.NameID, SUM(Ple.PledgeAmount) AS Pledged
FROM camPeople Peo, camPledges Ple, camAppeals App, camYears Yea
WHERE Peo.NameID = Ple.NameID (+)
AND NVL(Ple.AppealID,-1) = App.AppealID (+)
AND NVL(App.YearID,-1) = Yea.YearID (+)
AND Yea.YearDesc = '2004' /* SET YEAR TO DESIRED VALUE */
GROUP BY Peo.NameID
) PeopleGift,
(
SELECT NameID, RelatedNameID
FROM camRelationships
WHERE RelationTypeID = 4 /* SET DESIRED TYPE OF RELATION */
) SpousalRelationships,
(
SELECT NameID, RelatedNameID
FROM camRelationships
WHERE RelationTypeID = 4 /* SET DESIRED TYPE OF RELATION */
) DuplicateRelationships,
(
SELECT Peo.NameID, SUM(Ple.PledgeAmount) AS Pledged
FROM camPeople Peo, camPledges Ple, camAppeals App, camYears Yea
WHERE Peo.NameID = Ple.NameID (+)
AND NVL(Ple.AppealID,-1) = App.AppealID (+)
AND NVL(App.YearID,-1) = Yea.YearID (+)
AND Yea.YearDesc = '2004' /* SET YEAR TO DESIRED VALUE */
GROUP BY Peo.NameID
) SpousesGift,
camPeople SPeo,
camNamesV Nam,
camNamesV SNam

WHERE /* JOINS */
Peo.NameID = PeopleGift.NameID (+)
AND Peo.NameID = DuplicateRelationships.NameID (+)
AND DuplicateRelationships.NameID IS NULL
AND Peo.NameID = SpousalRelationships.RelatedNameID (+)
AND NVL(SpousalRelationships.NameID,-1) = SpousesGift.NameID (+)
AND NVL(SpousalRelationships.NameID,-1) = SPeo.NameID (+)
AND Peo.NameID = Nam.NameID
AND NVL(SpousalRelationships.NameID,-1) = SNam.NameID (+)
/* FILTERS */
AND NVL(PeopleGift.Pledged,0) + NVL(SpousesGift.Pledged,0) >= 500