I have a Table with Columns: StockItem,TransactionDate,QuantityOnDate(StockItem Quantity On The Transaction Date),Bin,Warehouse.I have Created a key with StockItem,Bin and Warehouse.So for a particular key there will be a list of Transaction Dates and quantities on those dates,This repeats for second key value and so on.
Now I need to calculate The quantity for all dates from 2006 to 2010,i.e I have to add the Dates in between the Date rows.The Quantity will be just the previous Quantity loaded,which i can do with The peek or previous function but how to add the missing Date rows in between.
For example for the first key, Quantity on 30/07/2010 is -22 and Quantity on 04/08/2010 is also -22.I need to add dates from 30/07/2010 to 04/08/2010 after The first row such that the value is the Quantity of 30/07/2010.
I need to add Dates from 01/01/2006 to todays date for every key.I can Calculate the Values on those Dates but 'How to add the Dates!' in the script itself?
If I am correct You want to create the ledger of stocks. For this first you have to create a dummy table which contains all the dates foe every key with 0 value and then concatenate it to your current transactional table. After concatenating you need to sort it on key and date and then use the peek function to call previous row value. Hope this will help you.