Add opening and closing balance columns
- by user1862899
i have a table like this:
StockNumber|InventoryName|Year|Month|Adj|iss|piss|Tsfr|return|rdj|rpo|xefr
alb001 clinic1 2010 1 4 5 5 5 6 5 4 10
alb001 Clinic1 2010 2 10 2 2 3 3 4 4 4
alb001 Clinic1 2010 4 11 3 5 77 90 78 9 6
alb001 Clinic1 2010 5 10 2 2 3 3 4 4 4
i want to add a closing balance column which will be
sum(return+rdj+rpo+xefr) - sum(adj+iss+piss+tsfr)
i also want to add the opening balance column which will be the be the closing balance of the previous month.
i will then calculate the current months balance as
OpeningBalance + sum(return+rdj+rpo+xefr) - sum(adj+iss+piss+tsfr) = ClosingBalance
NB.The Year and Month columns are floats and also want to change them to date format. i am newbie to sql and crystal reports....i want a query to help me achieve the tasks of developing a report that has the opening and closing balance columns,the opening balance being the previous closing balance.....thank you for your help....
