samedi 27 juin 2015

Fifo Method in SQL

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