How can I make this SQL Server script dynamic through the years?

I have code that determines the number of "lapsed" donors for each year. I have combined each year donors have lapsed using unions. I would like for this data to automatically update each year without adding in a union. Is there a way for the years to be dynamic?

I've only used the current static script I have to pull all lapsed donors since 2013. The provided script shows 2017-2019.

--Donated 2018, Not 2019
SELECT DISTINCT 
    PY.OppFiscalYear, COUNT(DISTINCT PY.AccountId)
FROM
    (SELECT DISTINCT PY.AccountId, PY.OppFiscalYear
     FROM reporting.AllGifts PY
     WHERE PY.OppFiscalYear = '2018'
       AND PY.OpportunityRecordType IN ('Classy', 'Donation', 'Pledge')
       AND PY.AccountRecordType = 'Household'
       AND PY.OppStage = 'Paid') PY
LEFT JOIN
    (SELECT DISTINCT Y.AccountId
     FROM reporting.AllGifts Y
     WHERE Y.OppFiscalYear = '2019'
       AND Y.OpportunityRecordType IN ('Classy', 'Donation', 'Pledge')
       AND Y.AccountRecordType = 'Household'
       AND Y.OppStage = 'Paid') Y ON PY.ACCOUNTID = Y.ACCOUNTID
WHERE 
    Y.AccountId IS NULL
GROUP BY 
    PY.OppFiscalYear

UNION

--Donated 2017, Not 2018
SELECT DISTINCT 
    PY.OppFiscalYear, COUNT(DISTINCT PY.AccountId)
FROM
    (SELECT DISTINCT PY.AccountId, PY.OppFiscalYear
     FROM reporting.AllGifts PY
     WHERE PY.OppFiscalYear = '2017'
       AND PY.OpportunityRecordType IN ('Classy', 'Donation', 'Pledge')
       AND PY.AccountRecordType = 'Household'
       AND PY.OppStage = 'Paid') PY
LEFT JOIN
    (SELECT DISTINCT Y.AccountId
     FROM reporting.AllGifts Y
     WHERE Y.OppFiscalYear = '2018'
       AND Y.OpportunityRecordType IN ('Classy', 'Donation', 'Pledge')
       AND Y.AccountRecordType = 'Household'
       AND Y.OppStage = 'Paid') Y ON PY.ACCOUNTID = Y.ACCOUNTID
WHERE 
    Y.AccountId IS NULL
GROUP BY 
    PY.OppFiscalYear

I expect the same output I have currently, but I want it to be able to automatically include any new donations added day by day. If I kept using my current method, the new script that would need to be added to the top with a union is for 2019 and 2020.

2 answers

  • answered 2019-05-21 15:58 Sean Lange

    Doesn't a basic aggregate work? I would think something this is what you are after.

    SELECT PY.OppFiscalYear
        , COUNT(DISTINCT PY.AccountId)
    FROM reporting.AllGifts PY
    WHERE PY.OpportunityRecordType IN ('Classy', 'Donation', 'Pledge')
        AND PY.AccountRecordType= 'Household'
        AND PY.OppStage = 'Paid'
    group by PY.OppFiscalYear
    

  • answered 2019-05-21 16:02 James

    Not sure how performant this will be depending on the size of your dataset but a correlated subquery should get the results you want:

    SELECT PY.OppFiscalYear, COUNT(DISTINCT PY.AccountId)
    FROM reporting.AllGifts PY
    WHERE 
    PY.OpportunityRecordType IN ('Classy', 'Donation', 'Pledge')
            AND PY.AccountRecordType= 'Household'
            AND PY.OppStage = 'Paid'
    AND NOT EXISTS
        (SELECT 1 FROM  reporting.AllGifts Y
    WHERE 
    Y.OpportunityRecordType IN ('Classy', 'Donation', 'Pledge')
            AND Y.AccountRecordType= 'Household'
            AND Y.OppStage = 'Paid'
    AND Y.OppFiscalYear = PY.OppFiscalYear + 1
    AND Y.AccountId = PY.AccountId)
    AND OppFiscalYear <> (SELECT MAX(OppFiscalYear) FROM reporting.AllGift) -- Can't be lapsed from the latest year!
    GROUP BY OppFiscalYear