Customers can receive (reward) points and use them for something. Each package of rewards points has a different value/size (number of points) and an expiration date sometime in the future which can be any time (no regularity in the expiration dates).
What is the formula to determine if points should be subtracted from the total point balance after a package of points has reached its expiration date?
Here an example in less abstract terms:
Everyone starts with 0 points. Then you get a package of 5 points which expires in 30 days. [balance: 5] After 20 days you spend 2 points. [balance: 3] After another 5 days (on day 25) you receive another package of 5 points, which also expires in 30 days. [balance: 8] After another 3 days, on day 28, just 2 days before the first package of points is about to expire, you spend another 2 points. [balance: 6] Here comes day 30 and your first package expires. 1 point has to be deducted, because it expired. Your balance is now 5.
Here is how I thought I could calculate it, but it doesn't work: you look at the day when someone received the package and when it expires (we'll call it lifetime of the package). You add all the deductions that were made, any points spend during the lifetime, and then see what the difference is between the original package value and all spending made during the lifetime. Now you subtract this number from the total balance (if the balance can accommodate it without dropping below 0, otherwise just make it so it's even 0).
So I thought subtract all transactions which took place during the lifetime of a package (in the example it's 4). the size of the package (5) minus 4 is 1, exactly how much had to be deducted at the end of the package lifetime.
But now consider this example:
You started out with a package of 10, which expires in 30 days. Then on day 10 you received a package of 5 which expires in 20 days. So they both expire at the same time, your total balance is 15. On day 20 you receive another package of 5 which expires in 20 days. Your total balance is 20, but in 10 days this balance should drop to 5, because the first package of 10 and the second package of 5 will expire. So now you spend 10 points at day 25. Your balance becomes 10. When using the formula I thought to use before, meaning to see if the total amount of points spent during the lifetime of the package is equal or greater to the original package value, nothing will have to expire. So that is true for the first package of 10 and the second package of 5. So on day 30 nothing expires and the balance remains 10, even though it should be only 5.
How do I calculate if points should be subtracted from the total balance when a package expires? How can I figure out if a package has been used or not?