SQL Running Sum with flags at certain amounts

Question

I am trying to create a query to sum total gifts given by a certain person, but to add a flag on the individual gift when the total giving hits $500 (I don't need to sum past that).

I have the Persons ID, the gift size, and the gift date. for example:

ID           gift_date       gift_amt
---------    -----------     --------------  
1            1/6/09          500
2            1/9/09          200
2            1/15/09         65
3            1/26/09         140
2            2/10/09         600
3            3/7/09          200

I would like a flag to be shown in my query for ID# 1 on the row with gift_date of 1/6/09, for ID#2 for gift date 2/10/09 and no flag for ID#3.

Answer 1

See if this helps you at all:

http://www.databasejournal.com/features/mssql/article.php/3112381/SQL-Server-Calculating-Running-Totals-Subtotals-and-Grand-Total-Without-a-Cursor.htm

Answer 2

In Oracle and PostgreSQL 8.4:

SELECT  q.*, CASE WHEN psum > 500 THEN 1 ELSE 0 END AS flag
FROM    (
        SELECT  gd.*, SUM(gift_amt) OVER (PARTITION BY id ORDER BY gift_date) AS psum
        FROM    gift_date gd
        ) q
WHERE   psum <= 500 - gift_amt

Answer 3

Typically this would be done by the View (as in Model-View-Controller). Most often this would be a grid control that has grouping functionality, rather than when the data is loaded. You would then load the data from the database as you showed above, and configured the view to display the grouped data.