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 :doc:`../CreateSqlScript`.

.. code-block:: sql

    --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