How do I find the sum of all transactions since an event?

Question

So, let's say that I have a group of donors, and they make donations on an irregular basis. I can put the donor name, the donation amount, and the donation date into a table, but then I want to do a report that shows all of that information PLUS the value of all donations after that amount.

I know that I can parse through this using a loop, but is there a better way?

I'm cheating here by not bothering with the code that would go through and assign a transaction number by donor and ensure that everything is the right order. That's easy enough.

    DECLARE @Donors TABLE (
          ID                INT IDENTITY
        , Name              NVARCHAR(30)
        , NID               INT
        , Amount            DECIMAL(7,2)
        , DonationDate      DATE
        , AmountAfter       DECIMAL(7,2)
        )

    INSERT INTO @Donors VALUES
          ('Adam Zephyr',1,100.00,'2017-01-14',NULL)
        , ('Adam Zephyr',2,200.00,'2017-01-17',NULL)
        , ('Adam Zephyr',3,150.00,'2017-01-20',NULL)
        , ('Braden Yu',1,50.00,'2017-01-11',NULL)
        , ('Braden Yu',2,75.00,'2017-01-19',NULL)

    DECLARE   @Counter1     INT = 0
            , @Name         NVARCHAR(30)

    WHILE @Counter1 < (SELECT MAX(ID) FROM @Donors)
        BEGIN
            SET @Counter1 += 1
            SET @Name = (SELECT Name FROM @Donors WHERE ID = @Counter1)
            UPDATE d1
                SET AmountAfter = (SELECT ISNULL(SUM(Amount),0) FROM @Donors d2 WHERE ID > @Counter1 AND Name = @Name)
                    FROM @Donors d1
                        WHERE d1.ID = @Counter1
        END

    SELECT * FROM @Donors

It seems like there ought to be a way to do this recursively, but I just can't wrap my head around it.

shawnt00 · Answer 1 · 2017-01-27T23:44:24.937

This would show the latest donation per Name which I presume is the donor and the total of all amounts donated by that person. Perhaps it's more appropriate to use NID for the partitions.

;with MostRecentDonations as (
    select *,
         row_number() over (partition by Name order by DonationDate desc) as rn,
         sum(Amount) over (partition by Name) as TotalDonations
    from @Donors
)
select * from MostRecentDonations
where rn = 1;

There's certainly no need to store a running total anywhere unless you have some kind of performance issue.

EDIT: I've thought about your question and now I'm thinking that you just want a running total with all the transactions included. That's easy too:

select *,
    sum(Amount) over (partition by Name order by DonationDate) as DonationsToDate
from @Donors
order by Name, DonationDate;

How do I find the sum of all transactions since an event?

1 Answers1