lundi 13 février 2017

Sql accounting Algorithm

I'm needing help to build an algorithm for calculating AgingIncome (at least that's what they're calling it). So we sell two different services (X and Y) for location A - Z. All of those sales come in and at the end of a month, I sum up by the type of service sold by location. For an example, Loc. A sold \$100 worth of service X. I want to account a part of that over the period of 8 years. First year, I want to account for 36% of that \$100, second year, 24%, 3rd year 12%, 4th year 8%, 5th year 7%, 6th year 6%, 7th year 5% and 8th year 2%. Since it's reported monthly, first year, first month would be 36/12=3% each month. Similarly, second year would be 24/12=2% per month and so on till the 8th year. Also, if on the second month, I sell another \$100 worth of service A at the same location, my calculation changes and that new amount is added to the remainder. i.e. month 2 would be 6%..

My idea of it is, at the end of each month, calculate for each location and type of service combination and build a table that spreads over the 8 year period, then going forward, just update that for each location? Any help with coming up with an algorithm or ideas to solve this problem would be appreciated.

Thanks.