Is there a set based solution for this problem?

Question

We have a table set up as follows:

|ID|EmployeeID|Date     |Category       |Hours|
|1 |1         |1/1/2010 |Vacation Earned|2.0  |
|2 |2         |2/12/2010|Vacation Earned|3.0  |
|3 |1         |2/4/2010 |Vacation Used  |1.0  |
|4 |2         |5/18/2010|Vacation Earned|2.0  |
|5 |2         |7/23/2010|Vacation Used  |4.0  |

The business rules are:

• Vacation balance is calculated by vacation earned minus vacation used.
• Vacation used is always applied against the oldest vacation earned amount first.

We need to return the rows for Vacation Earned that have not been offset by vacation used. If vacation used has only offset part of a vacation earned record, we need to return that record showing the difference. For example, using the above table, the result set would look like:

|ID|EmployeeID|Date     |Category       |Hours|
|1 |1         |1/1/2010 |Vacation Earned|1.0  |
|4 |2         |5/18/2010|Vacation Earned|1.0  |

Note that record 2 was eliminated because it was completely offset by used time, but records 1 and 4 were only partially used, so they were calculated and returned as such.

The only way we have thought of to do this is to get all of the vacation earned records in a temporary table. Then, get the total vacation used and loop through the temporary table, deleting the oldest record and subtracting that value from the total vacation used until the total vacation used is zero. We could clean it up for when the remaining vacation used is only part of the oldest vacation earned record. This would leave us with just the outstanding vacation earned records.

This works, but it is very inefficient and performs poorly. Also, the performance will just degrade over time as more and more records are added.

Are there any suggestions for a better solution, preferable set based? If not, we'll just have to go with this.

EDIT: This is a vendor database. We cannot modify the table structure in any way.

Answer 1

The following should do it..

(but as others mention, the best solution would be to adjust remaining vacations as they are spent..)

select 
    id, employeeid, date, category, 
    case 
    when  earned_so_far + hours - total_spent > hours then 
        hours 
    else 
        earned_so_far + hours - total_spent
    end as hours
from 
    (
                select 
                    id, employeeid, date, category, hours,
                    (
                        select 
                            isnull(sum(hours),0)
                        from 
                            vacations 
                        WHERE 
                            category = 'Vacation Earned' 
                            and 
                            date < v.date
                            and
                            employeeid = v.employeeid
                    ) as earned_so_far,
                    (
                        select
                            isnull(sum(hours),0)
                        from
                            vacations
                        where 
                            category = 'Vacation Used'
                            and 
                            employeeid = v.employeeid
                    ) as total_spent
                from 
                    vacations V
                where category = 'Vacation Earned'
    ) earned
where
    earned_so_far + hours > total_spent

The logic is

1. calculate for each earned row, the hours earned so far
2. calculate the total hours used for this user
3. select the record if the total_hours_so_far + hours of this record - total_spent_hours > 0