lapsed_giver_rock_id_only.sql

-- 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