GivingHouseholds Sql Script

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

 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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
--roles=Finance

declare @TagMembers varchar(50) = 'GivingHouseholds-Members'
declare @TagNonMembers varchar(50) = 'GivingHouseholds-ActiveNonMembers'
declare @TagBoth varchar(50) = 'GivingHouseholds-Combined'

drop table if exists #contributions
select Amount, PeopleId, FamilyId, CreditGiverId, CreditGiverId2 into #contributions
from dbo.Contributions2(@StartDt, @EndDt, 0, 0, null, 1, null)
where FundId = 180

;with Members as (
	select tp.PeopleId, p.FamilyId
	from dbo.TagPerson tp
	join dbo.Tag t on t.Id = tp.Id
	join dbo.People p on p.PeopleId = tp.PeopleId
	where t.Name = @TagMembers
	and t.TypeId = 101 -- QueryTag
),
NonMembers as (
	select tp.PeopleId, p.FamilyId
	from dbo.TagPerson tp
	join dbo.Tag t on t.Id = tp.Id
	join dbo.People p on p.PeopleId = tp.PeopleId
	where t.Name = @TagNonMembers
	and t.TypeId = 101 -- QueryTag
),
Both as (
	select tp.PeopleId, p.FamilyId
	from dbo.TagPerson tp
	join dbo.Tag t on t.Id = tp.Id
	join dbo.People p on p.PeopleId = tp.PeopleId
	where t.Name = @TagBoth
	and t.TypeId = 101 -- QueryTag
)
select cast('Member' as varchar) RowType, 
	count(distinct c.FamilyId) Cnt, 
	sum(c.Amount) Amount,
	(select count(distinct FamilyId) from Members) Households,
	@TagMembers Tag,
	@StartDt StartDt,
	@EndDt EndDt
from #contributions c
where exists(select null from Members where PeopleId in (c.CreditGiverId, c.CreditGiverId2))    

union all 

select 'NonMember', 
	count(distinct c.FamilyId) Cnt, 
	sum(c.Amount) Amount,
	(select count(distinct FamilyId) from NonMembers),
	@TagNonMembers Tag,
	@StartDt StartDt,
	@EndDt EndDt
from #contributions c
where exists(select null from NonMembers where PeopleId in (c.CreditGiverId, c.CreditGiverId2))    

union all 

select 'Combined', 
	count(distinct c.FamilyId) Cnt, 
	sum(c.Amount) Amount,
	(select count(distinct FamilyId) from Both),
	@TagBoth Tag,
	@StartDt StartDt,
	@EndDt EndDt
from #contributions c
where exists(select null from Both where PeopleId in (c.CreditGiverId, c.CreditGiverId2))