I have to implement something similar of a FIFO method in SQL. The thing is that I have points issued in different months and at the same time I have points exchanged in other date. The thing is that I have to know how many months ago were issued the points until the exchange. So the first points to be exchanged are the olders, but if 200 points were issued the 30/06/2014, and 100 points were issued the 31/07/2014, and the 31/08/2014 I exchanged 250 points. 200 points have 2 months of development (the 200 of June) and 50 points have one month of development (50 of 100 issued in July). How can I code this?
I leave some table to better understanding!
Thanks
Date of issue Number of account Issued points
30-abr 1 300
31-may 1 50
30-jun 1 100
30-jun 2 100
30-jun 3 120
31-may 4 20
30-jun 4 200
Date of exchange Number of account Exchanged points
30-jun 1 250
31-jul 1 200
31-jul 3 30
30-jun 4 30
31-jul 2 10
30-jun 3 30
31-ago 4 10
And the final table I need is.
Issue date Months after issue Exchanged points
30-abr 2,00 250,00
30-abr 3,00 50,00
31-may 2,00 50,00
31-may 1,00 20,00
30-jun 1,00 100,00
30-jun 1,00 30,00
30-jun 0,00 10,00
30-jun 1,00 10,00
30-jun 0,00 30,00
30-jun 2,00 10,00
Edit: Adding example to clarify the problem with account 1:
Points issued in 3 different months:
Date of issue Points
30-apr 300
31-may 50
30-jun 100
These are exchanged in 2 occasions:
Date of exchange Points
30-jun 250
31-jul 200
Since the exchange is happening from points issued in different months, the result should be 4 actions, so that the earliest points are used first:
Date of exchange Date of issue Points Months
30-jun 30-apr 250 2
31-jul 30-apr 50 3
31-jul 31-may 50 2
31-jul 30-jun 100 1
Aucun commentaire:
Enregistrer un commentaire