Giving Units (Date Range)¶
This SQL report looks at a giving unit as either an donor that gives individually or a couple that gives jointly and will present the total amount given during the date range. The report will sort by the last name and has the following columns:
- Name2 (last, then first)
- IsDeceased (true or false)
- Spouse (name)
- ContributionOptionsId (This is actually the Statement Type: 1=Individual; 2=Joint; 9=None)
- PositionInFamilyId (10=Primary Adult; 20=Secondary Adult; 30=Child)
This report uses a date range that is inside the script itself (lines 3 & 4). Because of that fact you may want to always run the report from Special Content instead of adding it to Reports in the Main Menu. This way you will be reminded of the date range in the script and can change it if necessary.
You may also be interested in the GivingUnits365 report. This is similar to this report, but it looks back 365 days from the current date. Click here to see that SQL recipe
Use the following code to Create the SQL Script. The recommended name of the script is GivingUnits. See How to create a SQL Script.
--Roles=Finance DECLARE @startdt DATETIME = '1/1/17' , @enddt DATETIME = '12/1/17' , @campusid INT = 0 , @pledges INT = 0 , @nontaxded INT = 0 , @includeUnclosed INT = 1 ;WITH givers AS ( SELECT FamilyId , CreditGiverId , SpouseId , SUM(Amount) AS Total FROM Contributions2(@startdt, @enddt, @campusid, @pledges, @nontaxded, @includeUnclosed, NULL) GROUP BY FamilyId, CreditGiverId, SpouseId ) SELECT p.Name2 ,p.IsDeceased ,p.PeopleId ,sp.FirstName Spouse ,sp.PeopleId SpouseID ,givers.Total ,givers.FamilyId ,p.ContributionOptionsId ,p.PositionInFamilyId FROM givers JOIN dbo.People p ON p.PeopleId = givers.CreditGiverId LEFT JOIN dbo.People sp ON sp.PeopleId = givers.SpouseId WHERE givers.Total > 1 AND p.IsDeceased = 0 ORDER BY p.Name2