Help with ideas,query or formula to achieve the task
Guys i have data in a table with columns:
Stock Number|Description|Inventory Name|Year|Month|ADJUSTMENT|ISSUE|PT ISSUE|RECEIVE ADJ|RECEIVE FROM PO|RECEIVE XFER| RETURN|TRANSFER
i want to add the opening and closing balance columns to this table....... the closing balance column will be as follows
(SUM(ABS(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + RETURN1)) - SUM(ABS(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER))) as Closing Balance...for the 1st month as per stocknumber,year and inventoryname.
the opening balance will be the closing balance of the previous month + (SUM(ABS(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + RETURN1)) of the current month minus SUM(ABS(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER))) of the current in order to get the closing balance for the current month which will b passed to the next month to be the opening balance......
please help with the script to get the query right..thank you for help in advance guys...