2 SQL: Givers by Amount and Monthly Consistency Shared by Josh Crews, Simple 3 years ago 8.0 Administration / Finance Intermediate 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 pageDECLARE @Today DATETIMESET @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'SELECTGivers.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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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.PersonIdLEFT 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