Recipe - SQL: Givers by Amount and Monthly Consistency

Skill level: Intermediate

Organization: Simple

Requires Rock: 1.8.0

---

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

Do you want to be able sort/filter your givers by amount given and how consistently they give?

Questions like

* at least $3,000/yr and gives in at least 11 out of the past 12 months?

* at least $10,000/yr and gives less than 6 months out of the past 12 months?

Run this in your SQL Command page

DECLARE @Today DATETIME
SET @Today = GETDATE()

-- Want to run from a specific date? Comment out the line `SET @Today = GETDATE()` and uncomment the line below and edit the date
-- SET @Today = '2019-12-31'

SELECT
Givers.PersonId,
-- By default this is commented out, uncomment to see Person's email
-- Person.Email,
Givers.Amount,
COALESCE(Month1.Gave,0) + COALESCE(Month2.Gave,0) + COALESCE(Month3.Gave,0) + COALESCE(Month4.Gave,0) + COALESCE(Month5.Gave,0) + COALESCE(Month6.Gave,0) + COALESCE(Month7.Gave,0) + COALESCE(Month8.Gave,0) + COALESCE(Month9.Gave,0) + COALESCE(Month10.Gave,0) + COALESCE(Month11.Gave,0) + COALESCE(Month12.Gave,0) [MonthsSum],
Month1.Gave [Month1],
Month2.Gave [Month2],
Month3.Gave [Month3],
Month4.Gave [Month4],
Month5.Gave [Month5],
Month6.Gave [Month6],
Month7.Gave [Month7],
Month8.Gave [Month8],
Month9.Gave [Month9],
Month10.Gave [Month10],
Month11.Gave [Month11],
Month12.Gave [Month12]
FROM (
  SELECT
    pa.PersonId,
    Sum(td.Amount) [Amount]
  FROM FinancialTransaction [t]
  INNER JOIN FinancialTransactionDetail [td] ON td.TransactionId = t.Id
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-12))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Givers]
JOIN Person on Person.Id = Givers.PersonId
LEFT JOIN (
  SELECT
    1 [Gave],
    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
) [Month1] ON Month1.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    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
) [Month2] ON Month2.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    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
) [Month3] ON Month3.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-5))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-4))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Month4] ON Month4.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-6))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-5))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Month5] ON Month5.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-7))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-6))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Month6] ON Month6.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-8))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-7))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Month7] ON Month7.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-9))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-8))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Month8] ON Month8.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-10))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-9))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Month9] ON Month9.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-11))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-10))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Month10] ON Month10.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-12))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-11))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Month11] ON Month11.PersonId = Givers.PersonId

LEFT JOIN (
  SELECT
    1 [Gave],
    PersonId
  FROM FinancialTransaction [t]
  INNER JOIN PersonAlias [pa]
    ON t.AuthorizedPersonAliasId = pa.Id
  WHERE t.TransactionDateTime >= DATEADD(DAY, 1, EOMONTH(@today,-13))
    AND t.TransactionDateTime < DATEADD(DAY, 1, EOMONTH(@today,-12))
    AND t.TransactionTypeValueId != 54
  GROUP BY
    pa.PersonId
) [Month12] ON Month12.PersonId = Givers.PersonId

Screenshots

• /GetImage.ashx?guid=9ae6d7b3-92a8-461a-b6e5-71dd52388e0f