opening and closing stock per warehouse

Question

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..

Answer 1

hello

Your Opening_balance going to be ever the Summary (Received_qty - consumption)

Best Regards

---

Leonardo Martínez

Answer 2

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.

Regards

Jitendra Gautam

Answer 3

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 

Best Regards

---

Leonardo Martínez

Answer 4

Hello Leonardo Martínez, Can u pls provide the code.. Regards Jitendra Gautam

Answer 5

The approperiate forum for the above question is below T-SQL Forum

http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

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

Answer 6

Hello Jitendra Gautam

Here you are the Code.. for the Trigger:

CREATE

TRIGGER    [dbo].[trigger_name] on [dbo].[Table_name]

for insert as 

Begin 

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

Where inward_date=inserted.inward_date

end

Best Regards

---

Leonardo Martínez