GivingForecast Sql Script

To install this script, copy all of the code below. Create a new Python document in Special Content using the name GivingForecast. 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
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
--roles=Finance

declare @FundId int = 180

declare @TagMembers varchar(50) = 'GivingForecast-Members'
declare @TagNonMembers varchar(50) = 'GivingForecast-ActiveNonMembers'

;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 PeopleId, FamilyId, 'Member' MemberStatus
	from Members
	union
	select PeopleId, FamilyId, 'NonMember'
	from NonMembers
),
FamilyCounts as (
	select 
		MemberStatus = 'Member',
		FamilyCnt = count(distinct FamilyId)
	from Members

	union all

	select 
		MemberStatus = 'NonMember',
		FamilyCnt = count(distinct FamilyId)
	from NonMembers
),
RecurTable as(
    select PeopleId
    from dbo.RecurringAmounts
    where (FundID = @FundId or @FundId is null)
    and Amt > 0
),
RecurringCounts as (
	select 
		MemberStatus = 'Member',
		RecurringCnt = count(distinct FamilyId)
	from Members m
	where exists(select null from RecurTable where PeopleId =  m.PeopleId)

	union all

	select 
		MemberStatus = 'NonMember',
		RecurringCnt = count(distinct FamilyId)
	from NonMembers n
	where exists(select null from RecurTable where PeopleId =  n.PeopleId)
),
RecurringAmts as (
	select 
		RecurringType = 'Monthly',
		b.MemberStatus,
		MonthlyAmt = isnull(sum(Amt),0)
	from dbo.RecurringAmounts a
	join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
	join Both b on b.PeopleId = mg.PeopleId
	where (a.fundid = @FundId or @FundId is null)
	and SemiEvery='E' and EveryN=1 and Period='M'
	group by b.MemberStatus

	union all

	select 
		RecurringType = 'Weekly',
		b.MemberStatus,
		MonthlyAmt = 4.345 * isnull(sum(Amt),0)
	from dbo.RecurringAmounts a
	join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
	join Both b on b.PeopleId = mg.PeopleId
	where (a.fundid = @FundId or @FundId is null)
	and SemiEvery='E' and EveryN=1 and Period='W'
	group by b.MemberStatus

	union all

	select 
		RecurringType = 'BiWeeky',
		b.MemberStatus,
		MonthlyAmt = 2.18 * isnull(sum(Amt),0)
	from dbo.RecurringAmounts a
	join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
	join Both b on b.PeopleId = mg.PeopleId
	where (a.fundid = @FundId or @FundId is null)
	and SemiEvery='E' and EveryN=2 and Period='W'
	group by b.MemberStatus

	union all

	select 
		RecurringType = 'SemiMonthly',
		b.MemberStatus,
		MonthlyAmt = 2 * isnull(sum(Amt),0)
	from dbo.RecurringAmounts a
	join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
	join Both b on b.PeopleId = mg.PeopleId
	where (a.fundid = @FundId or @FundId is null)
	and SemiEvery='S'
	group by b.MemberStatus
),
Totals as (
	select 
		MemberStatus, 
		MonthlyAmt = sum(MonthlyAmt)
	from RecurringAmts
	group by MemberStatus
)
select
  RowType = isnull(t.MemberStatus, 'Combined'),
  FamilyCnt = sum(FamilyCnt),
  RecurringCnt = sum(RecurringCnt),
  MonthlyAmt = sum(MonthlyAmt)
from Totals t
join FamilyCounts c on c.MemberStatus = t.MemberStatus
join RecurringCounts r on r.MemberStatus = t.MemberStatus
group by rollup(t.MemberStatus)