Hello everyone,
This is a relatively complex situation so I'll try to explain it as best as I can.
The application is refreshed everyday, and everyday, a new day is added (the new data from today). Here's a example of data close to what's going on in the application:
Product | DateAdded | NextAvailabilityDate | Flag
01 | 2019-04-01 | 2019-06-10 | X
02 | 2019-04-01 | 2019-04-03 | X
01 | 2019-04-02 | 2019-06-10 |
02 | 2019-04-02 | 2019-04-03 |
01 | 2019-04-03 | 2019-06-10 |
01 | 2019-04-04 | 2019-06-10 |
Think of this as a product, that, when it's goes in a state of back order, is added to this table with the timestamp from today and the date it should be available in the future. In the example above, we can see that the product 02 was made re-available on the 04-03 after a back order that started on the 04-01, which is why there are no new entries for the 02 product after the 04-02.
The flag symbolizes the start of a new back order sequence.
Now, if I were to run the script on the 04-04. Product 01 should count 4 days in back order and 0 changes on the availability date. Product 02 should count 0 days in back order and 0 changes as it isn't currently in back order.
Now, let's say, on the next day, the table looks like this:
Product | DateAdded | NextAvailabilityDate | Flag
01 | 2019-04-01 | 2019-06-10 | X
02 | 2019-04-01 | 2019-04-03 | X
01 | 2019-04-02 | 2019-06-10 |
02 | 2019-04-02 | 2019-04-03 |
01 | 2019-04-03 | 2019-06-10 |
01 | 2019-04-04 | 2019-06-10 |
01 | 2019-04-05 | 2019-06-15 |
02 | 2019-04-05 | 2019-05-05 | X
Two things happened, in the first place, the product 02 is in a new back order, which is flagged. The product 01 availability date changed. In this scenario, I would like to get, for product 01, 5 days in back order and 1 change of the availability date. For product 02, 1 day in back order and no changes of the availability date since it's the first date in this new sequence of back order.
The results of these calculations (the numbers of days in back order and the number of changes of the availability date in this sequence) would be stored in another table like so:
Product | DateBeginningBackOrder | NumbersOfAvailabilityDateChanges
01 | 2019-04-01 | 1
02 | 2019-04-01 | 0
02 | 2019-04-05 | 0
So, since the script is ran everyday, I need to make sure that when a new back order is flagged it's added to the table. I also need to make sure than when this back order is still on going, everyday I need to update the "NumbersOfAvailabilityDateChanges" for this specific back order.
I know this is a pretty long question but I'd take even a part of an answer at least to get me started since quite frankly I'm kind of lost at the moment.
If you made it this far, thank you for reading.
LesJean
