Couples With Different Contribution Options¶

This script shows couples who give but have different Contribution Statement Options. This identifies data integrity issues because they should match. This can only result from a conversion since TouchPoint forces these to stay in sync any time a change is made to either spouse.

The comment at the top of the script ensures that this report can only be run by a Finance user.

The recommended name is CouplesWithDifferentContributionOptions

Use the following code to Create the SQL Script. See How to create a SQL Script.

--Roles=Finance
DECLARE @td DATETIME = GETDATE()
DECLARE @fd DATETIME = DATEADD(DAY, -365, @td)
SELECT
    p.PeopleId,
    p.SpouseId,
    SUM(c.Amount) Total365,
    ev.Description HeadOption,
    ev2.Description SpouseOption
FROM dbo.People p
JOIN dbo.Families f ON f.FamilyId = p.FamilyId AND f.HeadOfHouseholdId = p.PeopleId
JOIN dbo.People sp ON sp.PeopleId = p.SpouseId
JOIN dbo.Contributions2(@fd, @td, 0, 0, NULL, 1, NULL) c ON c.CreditGiverId = p.PeopleId
LEFT JOIN lookup.EnvelopeOption ev ON ev.Id = p.ContributionOptionsId
LEFT JOIN lookup.EnvelopeOption ev2 ON ev2.Id = sp.ContributionOptionsId
WHERE ISNULL(p.ContributionOptionsId, 1) <> ISNULL(sp.ContributionOptionsId, 1)
GROUP BY p.PeopleId, p.SpouseId , ev.Description, ev2.Description