How to create view table for this query?

Question

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 26-Mar-13 2:02am

Karruksen

Updated 26-Mar-13 2:34am

Shanalal Kasim

v3

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Shanalal Kasim · Accepted Answer · 2013-03-26T02:33:00

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

Mike Meinz · Accepted Answer · 2013-03-26T02:36:00

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)[^]

How to create view table for this query?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us