Recipe - What share of regular givers have recurring giving setup?

Skill level: Intermediate

Organization: Simple

Requires Rock: 1.7.0

---

{# strip images & classes from the HTML but otherwise leave structure #}

Run this SQL

The first number is number of people who have given each of the last three months.

The second number is number of people with an active Scheduled Transaction (recurring giving) profile in Rock.

DECLARE @Today DATETIME
SET @Today = GETDATE()

SELECT
SUM(CASE WHEN TwoMonthsAgoGivers.PersonId IS NOT NULL AND ThreeMonthsAgoGivers.PersonId IS NOT NULL THEN 1 ELSE 0 END) [GaveEachMonthPast3Months],
COUNT(RecurringGivers.PersonId) [RecurringGivingSetup]
FROM (
  SELECT
    pa.PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime > DATEADD(DAY, 1, EOMONTH(@today,-2))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-1))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [LastMonthGivers]
LEFT JOIN (
  SELECT
    pa.PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime > DATEADD(DAY, 1, EOMONTH(@today,-3))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-2))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [TwoMonthsAgoGivers]
  ON TwoMonthsAgoGivers.PersonId = LastMonthGivers.PersonId
LEFT JOIN (
  SELECT
    pa.PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime > DATEADD(DAY, 1, EOMONTH(@today,-4))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-3))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [ThreeMonthsAgoGivers]
  ON ThreeMonthsAgoGivers.PersonId = LastMonthGivers.PersonId
LEFT JOIN (
  SELECT
    pa.PersonId
  FROM FinancialScheduledTransaction [fst]
  INNER JOIN PersonAlias [pa]
     ON fst.AuthorizedPersonAliasId = pa.Id
  WHERE fst.IsActive = 1
  GROUP BY
    pa.PersonId
) [RecurringGivers]
  ON RecurringGivers.PersonId = LastMonthGivers.PersonId

Screenshots

• /GetImage.ashx?guid=72ff6731-d8f9-43f3-bb70-6da7c80b7d3f