4 What share of regular givers have recurring giving setup? Shared by Josh Crews, Simple 4 years ago 7.0 Administration / Finance Intermediate Run this SQLThe 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