Thursday, May 24, 2012 12:24 PM
Now i want to add the opening balance in it and it should comes according to the closing balance and at first it is zero.
Just like that-
inward_date opening_bal received_qty consumption closing_bal
2/12/2010 0 111.39 101.28 10.11
3/12/2010 10.11 - - -
reply fast please..
- Edited by Jitendra Gautam Friday, May 25, 2012 5:10 AM
Thursday, May 24, 2012 1:19 PM
Your Opening_balance going to be ever the Summary (Received_qty - consumption)
Friday, May 25, 2012 4:45 AM
hello Leonardo Martínez,
I want that my closing balance is my opening balance for the next row.
i want that type of query that can solve my problem.
i am trying but not found the suitable query.
Friday, May 25, 2012 2:07 PM
Hello Jitendra Gautam
So.. in that case you must a create Trigger Procedure.. You need Opening_bal going to be Closing_bal The other Day..
I believe that you have a table Resume
Monday, May 28, 2012 7:00 AMHello Leonardo Martínez, Can u pls provide the code.. Regards Jitendra Gautam
Monday, May 28, 2012 8:13 AM
The approperiate forum for the above question is below T-SQL Forum
Anyways , this can be acheived by self join and rank as below
DECLARE @tbl TABLE ( Inward_date datetime ,receipt decimal ,consumption decimal ,closing_balance decimal ) insert into @tbl select '14-may-2012',77.27,231.81,-154.54 union select '15-may-2012',216.25,1714.77,-1498.52 union select '16-may-2012',163.55,981.30,-817.75 SELECT T.*,Opening_balance = CASE WHEN X.RNK IS NULL THEN 0 ELSE X.closing_balance END FROM ( SELECT *,RNK=RANK() OVER (ORDER BY inward_date) FROM @tbl ) T LEFT JOIN ( SELECT *,RNK=RANK() OVER (ORDER BY inward_date) FROM @tbl ) X ON T.RNK = X.RNK+1
Monday, May 28, 2012 1:12 PM
Hello Jitendra Gautam
Here you are the Code.. for the Trigger:
TRIGGER [dbo].[trigger_name] on [dbo].[Table_name]
for insert as
Declare @Open_balance as Numeric(10,2)
Select @Open_balance =(Received_qty - consumption) From Table_name Where inward_date=(select MAX(inward_date) from Table_name Where inward_date<>inserted.inward_date)
Update table_name Set Open_bal=@Open_balance
- Marked As Answer by Jitendra Gautam Tuesday, May 29, 2012 5:36 AM