Accumulating Rolling Total

I'm trying to create an accumulating field based on a set of records. I need to fill in daily amount balances that accumulates on a daily basis. But I can't seem to figure out how to create a total for the daily dates and have it add on additional amounts if needed.

Here's some sample data:

5 6 20 1 200.00 5/5/20000
5 6 20 1 -149.00 5/8/2000

5 6 20 1 100.00 5/10/2000

Now I already have a table with the dates created via a stored procedure. I have a set of dates from 5/5/2000 to 5/8/2000. So that results set should look like this:

5 6 20 1 200.00 5/5/2000

5 6 20 1 200.00 5/6/20000
5 6 20 1 200.00 5/7/2000

5 6 20 1 51.00 5/8/2000

5 6 20 1 51.00 5/9/2000

5 6 20 1 151.00 5/10/2000

....

I'm trying to creating a rolling sum that accumulates the amount field for each daily record and if a new amount is listed, then roll that amount into the total. If you have any suggestions about how to perform this rolling total via TSQL or SSIS, I would greatly appreciate it.

Thanks

Greg

Its complicated but (I think) achievable. You'll probably need a list of all contiguous dates to start with. Then join that list to your balances data as shown above. You will need to join on all days from the balances data that are less than or equal to the date in the list of dates. Then do a sum of all balances grouping by all the dates in the list of dates.

Its alot easier to achieve than it is to explain believe me

-Jamie

Oh P.S. I'm not sure you'll be able to do this in SSIS because MERGE JOIN doesn't support non-equi joins. Yet.