Your question was easy to answer because you gave a small example with test data that I could paste directly into SQL Server Management Express. I'll give you a five for that.
It is not a good idea to use the value of the IDENTITY column (ID) to make decisions.
I used Joe Celko's "
SQL For Smarties: Advanced SQL Programming" book for an example on how to do Opening and Closing Balances..
DECLARE @pind TABLE (ID INT IDENTITY(1,1),ICODE INT, ITEM VARCHAR(100),
MONTHS SMALLINT,YEARS SMALLINT,OPEN_BALANCE NUMERIC(10,3),GRN NUMERIC(10,3),MRS NUMERIC(10,3),
MRN NUMERIC(10,3), STOCK NUMERIC(10,3))
INSERT INTO @Pind (ICODE, ITEM, MONTHS, YEARS, GRN, MRS, MRN, STOCK)
SELECT 179,'BRAKE CHAMBER',4,2010,32,NULL,1,33 UNION ALL
SELECT 179,'BRAKE CHAMBER',7,2010,10,NULL,1,11 UNION ALL
SELECT 179,'BRAKE CHAMBER',8,2010,12,NULL,1,13 ;
SELECT M.ICODE, M.ITEM, M.MONTHS, M.YEARS,
ISNULL((select SUM((ISNULL(OB.GRN,0.0)- ISNULL(OB.MRS,0.0)+ ISNULL(OB.MRN,0.0))) from @Pind as OB
where ((OB.years*100)+OB.months)<((m.years*100)+m.months) AND OB.ICODE=m.ICODE),0)
AS [Opening Balance],
M.GRN, M.MRS, M.MRN, M.STOCK ,
(select SUM((ISNULL(CB.GRN,0.0)- ISNULL(CB.MRS,0.0)+ ISNULL(CB.MRN,0.0))) from @Pind as CB where ((CB.years*100)+CB.months)<=((m.years*100)+m.months) AND CB.ICODE=m.ICODE)
AS [Closing Balance] FROM @Pind as m
Update m set OPEN_BALANCE=
ISNULL((select SUM((ISNULL(OB.GRN,0.0)- ISNULL(OB.MRS,0.0)+ ISNULL(OB.MRN,0.0))) from @Pind as OB
where ((OB.years*100)+OB.months)<((m.years*100)+m.months) AND OB.ICODE=m.ICODE),0) from @PIND as m
ICODE ITEM MONTHS YEARS Opening Balance GRN MRS MRN STOCK Closing Balance
179 BRAKE CHAMBER 4 2010 0.000 32.000 NULL 1.000 33.000 33.000
179 BRAKE CHAMBER 7 2010 33.000 10.000 NULL 1.000 11.000 44.000
179 BRAKE CHAMBER 8 2010 44.000 12.000 NULL 1.000 13.000 57.000
Tested: SQL Server Express 2012
To create a View
Create View MyView As
SELECT M.ICODE, M.ITEM, M.MONTHS, M.YEARS,
ISNULL((select SUM((ISNULL(OB.GRN,0.0)- ISNULL(OB.MRS,0.0)+ ISNULL(OB.MRN,0.0))) from @Pind as OB
where ((OB.years*100)+OB.months)<((m.years*100)+m.months) AND OB.ICODE=m.ICODE),0)
AS [Opening Balance],
M.GRN, M.MRS, M.MRN, M.STOCK ,
(select SUM((ISNULL(CB.GRN,0.0)- ISNULL(CB.MRS,0.0)+ ISNULL(CB.MRN,0.0))) from @Pind as CB where ((CB.years*100)+CB.months)<=((m.years*100)+m.months) AND CB.ICODE=m.ICODE)
AS [Closing Balance] FROM @Pind as m
Documentation:
Create View (Transact SQL)[
^]