Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- This is variant of the Rock Lapsed Giver SQL, which all the Select fields removed so that you can use it in the DataToolkit Rock Plugin SQL->Dataview tool.
-- Full SQL: https://gist.github.com/joshcrews/e010290be730a77250ba3da385b21911
-- For questions email josh@simpledonation.com
DECLARE @ANNUAL_GIVING_THRESHOLD AS INTEGER = 3000;
DECLARE @MAXIMUM_RECENT_GIVING_DOLLARS AS INTEGER = 100;
DECLARE @RECENT_PERIOD_WEEKS AS TINYINT = 8;
SELECT
Person.Id
FROM Person
LEFT JOIN (
SELECT
SUM(td.Amount) [Amount],
PA.PersonId
FROM FinancialTransaction [t]
INNER JOIN FinancialTransactionDetail [td]
ON t.Id = td.TransactionId
INNER JOIN PersonAlias [PA]
ON t.AuthorizedPersonAliasId = PA.Id
WHERE
t.TransactionTypeValueId != 54
AND
t.TransactionDateTime < DATEADD(year, -1, getdate())
AND
t.TransactionDateTime > DATEADD(week, -1 * @RECENT_PERIOD_WEEKS, DATEADD(year, -1, getdate()))
GROUP BY PA.PersonId
) [SamePeriodLastYear] ON SamePeriodLastYear.PersonId = Person.Id
LEFT JOIN (
SELECT
SUM(td.Amount) [Amount],
PA.PersonId
FROM FinancialTransaction t
INNER JOIN FinancialTransactionDetail [td]
ON t.Id = td.TransactionId
INNER JOIN PersonAlias [PA]
ON t.AuthorizedPersonAliasId = PA.Id
WHERE
t.TransactionTypeValueId != 54
AND
t.TransactionDateTime > DATEADD(week, -1 * @RECENT_PERIOD_WEEKS, DATEADD(year, -1, getdate()))
GROUP BY PA.PersonId
) [PastYear] ON PastYear.PersonId = Person.Id
LEFT JOIN (
SELECT
SUM(td.Amount) [Amount],
PA.PersonId
FROM FinancialTransaction [t]
INNER JOIN FinancialTransactionDetail [td]
ON t.Id = td.TransactionId
INNER JOIN PersonAlias [PA]
ON t.AuthorizedPersonAliasId = PA.Id
WHERE
t.TransactionTypeValueId != 54
AND
t.TransactionDateTime > DATEADD(week, -1 * @RECENT_PERIOD_WEEKS, getdate())
GROUP BY PA.PersonId
) [RecentPeriod] ON RecentPeriod.PersonId = Person.Id
LEFT JOIN (
SELECT
COUNT(fst.Id) [Count],
PA.PersonId
FROM FinancialScheduledTransaction [fst]
INNER JOIN PersonAlias [PA]
ON fst.AuthorizedPersonAliasId = PA.Id
WHERE fst.IsActive = 1
GROUP BY PA.PersonId
) [FinancialScheduledTransactions] ON FinancialScheduledTransactions.PersonId = Person.Id
WHERE PastYear.Amount IS NOT NULL
AND PastYear.Amount > @ANNUAL_GIVING_THRESHOLD
AND (RecentPeriod.Amount < @MAXIMUM_RECENT_GIVING_DOLLARS OR RecentPeriod.Amount IS NULL)
AND (SamePeriodLastYear.Amount / PastYear.Amount) > ((@RECENT_PERIOD_WEEKS * 1.0) / 60) -- and last year this period they gave a normal amount for them
AND FinancialScheduledTransactions.Count IS NULL
ORDER BY Email ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment