GivingTypesData SQL Script

This SQL script produces a two column set of data rows. The first column named FundsTypeRange is a combination of the TagName concatenated with the date range descriptor. The second column named Total is the sum of the ContributionAmount. Every Amount displayed in the final report is found in this set of data.

To install this script, copy all of the code below. Create a new Sql Script document in Special Content using the name GivingTypesData. Then paste the code into editor and save.

Explanation of the Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
--Roles=Finance

;with FundSetTags as (
	select 
		ContributionId,
		[Priority],
		FundSet = Priority / 100,
		TagName
	from dbo.ContributionTag t
	where TagName like 'Gt%'
	and Priority > 0
),
Tags as (
	select 
		ContributionId,
		MinPriority = (select min(Priority) from FundSetTags where ContributionId = t.ContributionId and FundSet = t.FundSet),
		[Priority],
		FundSet,
		TagName
	from FundSetTags t
)
select 
	FundsTypeRange = t.TagName + 'Ytd', 
	Total = sum(c.ContributionAmount)
from Tags t
join dbo.Contribution c on c.ContributionId = t.ContributionId
where exists(select null from ContributionTag where TagName like 'GtrYtd' and ContributionId = t.ContributionId)
and (t.MinPriority = t.Priority or t.Priority = FundSet * 100 + 99)
group by t.TagName, t.FundSet

union all

select 
	FundsTypeRange = t.TagName + '30',
	Total = sum(c.ContributionAmount)
from Tags t
join dbo.Contribution c on c.ContributionId = t.ContributionId
where exists(select null from ContributionTag where TagName like 'Gtr30' and ContributionId = t.ContributionId)
and (t.MinPriority = t.Priority or t.Priority = FundSet * 100 + 99)
group by t.TagName, t.FundSet

union all

select 
	FundsTypeRange = t.TagName + '365', 
	Total = sum(c.ContributionAmount)
from Tags t
join dbo.Contribution c on c.ContributionId = t.ContributionId
where exists(select null from ContributionTag where TagName like 'Gtr365' and ContributionId = t.ContributionId)
and (t.MinPriority = t.Priority or t.Priority = FundSet * 100 + 99)
group by t.TagName, t.FundSet

order by FundsTypeRange

Line 16 establishes a MinPriority per contribution so that tags with more than one Priority for a given Contribution can be limited to the one with the MinPriority.

Lines 27, 38, and 49 are the clauses that limit the contributions to the date range. This is done by seeing whether the contribution exists in the date range ContributionTag called one of: GtrYtd, Gtr30, and Gtr365. These date range ContributionTags will not have a priority value. The t.Priority > 0 on Line 11 what limits the ContributionTags to just the combination FundSet / RowType tags.

Lines 28, 39, and 50 are what limit the Contributions to one per FundSet / RowType by using the clause MinPriority = t.Priority

The three select statements limited by date ranges are all unioned together for a single set of data.

If there are 12 RowTypes, then there will be (12 RowTypes + 1 Total) * 3 DateRanges * 3 FundSets which is as many as 117 rows delivered by this SQL script. Some combinations of FundSet/RowType/DateRange may not include any contributions and thus will not be included in the results.