mardi 14 juin 2016

SQL, Stock movements where no zero stock records

I'm trying to create a stock movement file from historical stock records, see picture:

http://i.stack.imgur.com/tDJI2.png

I've been able to get the green movements and the orange value (had some luck union all to a 0 stock record for max(day) + 1 and removing anything less than today). Movements are then generated with a lag function.

The difficulty is there are no 0 stock records. i.e. fifth row in the example above there is no record so unable to create the correct movement to 0 with the -2. It would create movements of -10, -4, -4, 5, -3, -1, -3

Is there any easier way to identify the gaps and create phantom 0's, I know the dates (these are a mix of weekly that will become daily)?

Aucun commentaire:

Enregistrer un commentaire