Click here to Skip to main content
15,917,645 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi,
Good Evening everybody

Domain: SQL Server


Query:
SQL
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.ID,(ROW_NUMBER() OVER(PARTITION BY M.ICODE,months ORDER BY M.ICODE,months)) 
'RowNumber', M.ICODE, M.ITEM, M.MONTHS, M.YEARS,M.OPEN_BALANCE, M.GRN, M.MRS, M.MRN, M.STOCK ,
(SELECT SUM((ISNULL(GRN,0.0)- ISNULL(MRS,0.0)+ ISNULL(MRN,0.0))) 
 FROM @Pind WHERE  ID<=M.ID AND ICODE=M.ICODE) as CLOSE_BALANCE
FROM @Pind as M



"Here,I add a new column that name is "Open_Balance".now i want to initialize zero on first column in 'open_balance' then the first row 'Close_Balance' value will be come to second row 'open_balance' column..How is it possible? pls solve this problem.. then finally create view table for this query..

Thank & Regards
Karthick
Posted
Updated 26-Mar-13 2:34am
v3
Comments
Karruksen 26-Mar-13 8:15am    
hi gvprabu,
i need one more help...pls solve this problem...
[no name] 26-Mar-13 8:15am    
To implement this you can use cursor also.
Mike Meinz 26-Mar-13 8:59am    
I updated Solution 2 to include an UPDATE and a VIEW statement.

Note: You should not use the IDENTITY column (ID) to make decisions in your SQL. I used the "month" and "year" column.

Hi,

Try below


SQL
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



update b  set OPEN_BALANCE = a.close_balance from  @Pind b inner join (
select id,(SELECT SUM((ISNULL(GRN,0.0)- ISNULL(MRS,0.0)+ ISNULL(MRN,0.0)))
FROM @Pind WHERE ID<=M.ID AND ICODE=M.ICODE) as CLOSE_BALANCE
FROM @Pind as M
) a on a.ID =  b.ID -1

SELECT M.ID,(ROW_NUMBER() OVER(PARTITION BY M.ICODE,months ORDER BY M.ICODE,months))
'RowNumber', M.ICODE, M.ITEM, M.MONTHS, M.YEARS,M.OPEN_BALANCE, M.GRN, M.MRS, M.MRN, M.STOCK ,
(SELECT SUM((ISNULL(GRN,0.0)- ISNULL(MRS,0.0)+ ISNULL(MRN,0.0)))
FROM @Pind WHERE ID<=M.ID AND ICODE=M.ICODE) as CLOSE_BALANCE
FROM @Pind as M
 
Share this answer
 
Comments
Karruksen 26-Mar-13 8:47am    
This is Good answer but i also want how to create view table for this ?
Karruksen 27-Mar-13 1:26am    
Thanks a lot sir.
Shanalal Kasim 27-Mar-13 1:47am    
Thanks
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..

SQL
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 ;
--
-- Computes both Opening and Closing Balance
--
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



SQL
--Computes and sets Opening Balance
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
SQL
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)[^]
 
Share this answer
 
v7
Comments
Karruksen 26-Mar-13 8:46am    
This is Good answer but i also want how to create view table for this ?
Mike Meinz 26-Mar-13 9:06am    
See revised Solution 2.
Karruksen 27-Mar-13 0:41am    
Msg 111, Level 15, State 1, Line 15
'CREATE VIEW' must be the first statement in a query batch.
Mike Meinz 27-Mar-13 5:41am    
Yes. You can't use CREATE VIEW in your test scenario. It should work, though, when you submit it to an existing database rather than a table that you created in a SSMS Query Window.
Karruksen 27-Mar-13 1:26am    
Thank You sir... i am happy with your solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900