sorry for not being specific.
There are cases in which I need to do cumulative totals at each date for a given period of time. For instance, G/L Account balance at each date through out the entire fiscal year. Or, Item inventory at each date for a given period of time. The most common approach I have seen in many different systems is to create a kind of index table which consists of concerned elements, e.g.
"GL Account No." Date Balance
1100 09/08/2014 10
1100 09/09/2014 20
1100 09/10/2014 30
....
1200 09/08/2014 10
1200 09/09/2014 20
1200 09/10/2014 30
....Whenever there is transaction happening to G/L Account No, we try to maintain this index sum table. So that we can pull it out later for cumulative balance reporting. The same logic could be implemented for other similar requirements. The problem with this approach is that by the time it goes, this table becomes increasingly large and very “expensive” to maintain. Sometimes, it gets so bad that the system becomes really slow and we cant afford to maintain this table during transaction update. And, technically, it can be quite challenging to write the code to maintain this table, especially when the criterio becomes complex.
There is a different approach which I have seen in another system that is written in C++. It implements a kind of virtual date table like a built-in date calender which has a table structure like this:
Date Name
01/22/0001 Monday
01/23/0001 Tuesday
01/24/0001 Wednesday
01/25/0001 Thursday
01/26/0001 Friday
01/27/0001 Saturday
01/28/0001 Sunday
...
01/01/9998
01/01/9999The data in this table is not physically stored in the database but dynamically populated in memory. In this case, if I want to calculate G/L Balance at date, I simply do a G/L Account left join Date, then using this G/L Account No. and each specific date, one can easily filter out the transactions and calculate the totals accordingly. This way, there is no need to create or maintain any additional tables with totals. And it is easy to change the logic of calculation in case criterio changes without physically affecting the database, in fact, with Rules, one can just create as many derived columns as they need against with this Date table and attach them on fly for different needs.