IN SQL Server, First row Last Column is How to add with second row Columns?

Question

4.00/5 (1 vote)

See more:

Hello Sir,

DOMAIN: SQL SERVER
Table Name: pindex

ICODE       ITEM      MONTHS   YEARS    GRN     MRS    MRN      STOCK
33	  CLUTCH	4	2010	1.000	 0	1	2.000
155	  HOSE          4	2010	2.000	 0      1	3.000
179	  BRAKE CHAMBER	4	2010	2.000	 0	1	3.000
179	  BRAKE CHAMBER	4	2010	20.000	 0	1	21.000
179	  BRAKE CHAMBER	4	2010	32.000	 0	1	33.000
409	  ENGINE OIL	4	2010	210.000	 0	11	221.000
419	  FUEL	        4	2010	200.000	 0	0	200.000
419	  FUEL	        4	2010	210.000	 0	0	210.000
551	  DRIVER SEAT	4	2010	10.000	 0	19	29.000
602	  WIRE	        4	2010	272.000	 0	14	286.000
771	  WASTE	        4	2010	500.000	 0	0	500.000
771	  WASTE	        4	2010	500.000	 0	4	504.000
771	  WASTE	        4	2010	1500.00  0	0	1500.000
771	  WASTE	        4	2010	1500.00  0	4	1504.000

Formula: STOCK=GRN-MRS+MRN

Here, i did one calculation that is STOCK=GRN-MRS+MRN. Now I want "How to add last column(STOCK) of first row into second row of some columns(GRN) and then do same calculation(STOCK=STOCK+GRN-MRS+MRN) and getting stock new value.This process will continue till ICODE is same. "
for example table:

icode	Months	Years	grn	       mrs	mrn	stock
1	4	2010	400	       200	50	250
1	4	2010	60+(250)=310	300	0	oldvalue(60)  newvalue(10)
1	4	2010	40+(10)=50	0	0	oldvalue(40)  newvalue(50)

Pls sir ...Find result query for this Problem..

Posted 25-Mar-13 1:32am

Karruksen

Updated 25-Mar-13 7:52am

Maciej Los

v4

Add a Solution

Comments

Karruksen 25-Mar-13 7:42am

pls edit this question.. i don't know this is..

Karruksen 25-Mar-13 8:14am

gvprabu sir..pls solve this problem..

gvprabu 25-Mar-13 8:46am

I got ur Data... I will Solve and give u the Solution ASAP.

Karruksen 25-Mar-13 9:21am

Thank u and i will wait for your Best solution..

CHill60 25-Mar-13 10:22am

What version of SQL-Server are you using? (I only ask because some solutions don't work in SQL2005 or earlier)

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)

gvprabu · Accepted Answer · 2013-03-25T06:47:00

Hi Friend,

Check my Solution... In your Table, U have Unique ID Column means Use the Following Solution.
Else Create Temp Table with ID (IDENTITY) Column and Use the Same Solution.

SQL

DECLARE @pindex TABLE (ID INT IDENTITY(1,1),ICODE INT, ITEM VARCHAR(100), MONTHS SMALLINT,YEARS SMALLINT,GRN NUMERIC(10,3),MRS NUMERIC(10,3),MRN NUMERIC(10,3), STOCK NUMERIC(10,3))

INSERT INTO @Pindex (ICODE, ITEM, MONTHS, YEARS, GRN, MRS, MRN, STOCK)
SELECT 33,'CLUTCH',4,2010,1,NULL,1,2 UNION ALL
SELECT 155 ,'HOSE',4,2010,2,NULL,1,3 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,2,NULL,1,3 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,20,NULL,1,21 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,32,NULL,1,33 UNION ALL
SELECT 409,'ENGINE OIL',4,2010,210,NULL,11,221 UNION ALL
SELECT 419,'FUEL',4,2010,200,NULL,0,200 UNION ALL
SELECT 419,'FUEL',4,2010,210,NULL,0,210 UNION ALL
SELECT 551,'DRIVER SEAT',4,2010,10,NULL,19,29 UNION ALL
SELECT 602,'WIRE',4,2010,272,NULL,14,286 UNION ALL
SELECT 771,'WASTE',4,2010,500,NULL,0,500 UNION ALL
SELECT 771,'WASTE',4,2010,500,NULL,4,504 UNION ALL
SELECT 771,'WASTE',4,2010,1500,NULL,0,1500 UNION ALL
SELECT 771,'WASTE',4,2010,1500,NULL,4,1504 UNION ALL
SELECT 997,'SILENCER',4,2010,5,NULL,0,5 UNION ALL
SELECT 1362,'REFLECTOR',4,2010,250,NULL,50,300 UNION ALL
SELECT 1363,'REFLECTOR',4,2010,200,NULL,50,250 UNION ALL
SELECT 409,'ENGINE OIL',5,2010,210,NULL,11,221 UNION ALL
SELECT 419,'FUEL',5,2010,210,NULL,0,210 UNION ALL
SELECT 1616,'COVER',5,2010,10,NULL,1,11 UNION ALL
SELECT 1921,'U-BOLT',5,2010,1,NULL,4,5 UNION ALL
SELECT 1921,'U-BOLT',5,2010,12,NULL,4,16 UNION ALL
SELECT 1921,'U-BOLT',5,2010,20,NULL,4,24 UNION ALL
SELECT 419,'FUEL',6,2010,20,NULL,0,20 UNION ALL
SELECT 419,'FUEL',6,2010,143,NULL,0,143 UNION ALL
SELECT 771,'WASTE',6,2010,500,NULL,0,500 UNION ALL
SELECT 771,'WASTE',6,2010,500,NULL,4,504 UNION ALL
SELECT 1362,'REFLECTOR',6,2010,750,NULL,50,800 UNION ALL
SELECT 1363,'REFLECTOR',6,2010,750,NULL,50,800 UNION ALL
SELECT 1393,'PLATE',6,2010,78.5,NULL,80,158.5 UNION ALL
SELECT 1921,'U-BOLT',6,2010,10,NULL,4,14 UNION ALL
SELECT 155,'HOSE',7,2010,20,NULL,1,21 UNION ALL
SELECT 155,'HOSE',7,2010,30,NULL,1,31 UNION ALL
SELECT 179,'BRAKE CHAMBER',7,2010,4,NULL,1,5 UNION ALL
SELECT 179,'BRAKE CHAMBER',7,2010,6,NULL,1,7 UNION ALL
SELECT 179,'BRAKE CHAMBER',7,2010,8,NULL,1,9 UNION ALL
SELECT 179,'BRAKE CHAMBER',7,2010,10,NULL,1,11 UNION ALL
SELECT 409,'ENGINE OIL',7,2010,420,NULL,11,431 UNION ALL
SELECT 419,'FUEL',7,2010,180,NULL,0,180 UNION ALL
SELECT 419,'FUEL',7,2010,200,NULL,0,200 UNION ALL
SELECT 419,'FUEL',7,2010,210,NULL,0,210 UNION ALL
SELECT 419,'FUEL',7,2010,270,NULL,0,270 UNION ALL
SELECT 551,'DRIVER SEAT',7,2010,19,NULL,19,38 UNION ALL
SELECT 771,'WASTE',7,2010,500,NULL,0,500 UNION ALL
SELECT 771,'WASTE',7,2010,500,NULL,4,504 UNION ALL
SELECT 771,'WASTE',7,2010,750,NULL,0,750 UNION ALL
SELECT 771,'WASTE',7,2010,750,NULL,4,754 UNION ALL
SELECT 771,'WASTE',7,2010,1000,NULL,0,1000 UNION ALL
SELECT 771,'WASTE',7,2010,1000,NULL,4,1004 UNION ALL
SELECT 997,'SILENCER',7,2010,10,NULL,0,10 UNION ALL
SELECT 1362,'REFLECTOR',7,2010,500,NULL,50,550 UNION ALL
SELECT 1921,'U-BOLT',7,2010,20,NULL,4,24 UNION ALL
SELECT 2980,'TIGHTER',7,2010,150,NULL,3,153 UNION ALL
SELECT 3786,'D-SHAKLE',7,2010,400,NULL,6,406 UNION ALL
SELECT 64,'BELT',8,2010,10,NULL,0,10 UNION ALL
SELECT 66,'ENGINE BED',8,2010,10,NULL,0,10 UNION ALL
SELECT 179,'BRAKE CHAMBER',8,2010,12,NULL,1,13 UNION ALL
SELECT 409,'ENGINE OIL',8,2010,36,NULL,11,47 UNION ALL
SELECT 419,'FUEL',8,2010,30,NULL,0,30 UNION ALL
SELECT 419,'FUEL',8,2010,210,NULL,0,210 UNION ALL
SELECT 551,'DRIVER SEAT',8,2010,10,NULL,19,29 UNION ALL
SELECT 771,'WASTE',8,2010,50,NULL,0,50 UNION ALL
SELECT 771,'WASTE',8,2010,50,NULL,4,54 UNION ALL
SELECT 1362,'REFLECTOR',8,2010,50,NULL,50,100 UNION ALL
SELECT 1362,'REFLECTOR',8,2010,100,NULL,50,150 UNION ALL
SELECT 1363,'REFLECTOR',8,2010,50,NULL,50,100 UNION ALL
SELECT 2980,'TIGHTER',8,2010,16,NULL,3,19 UNION ALL
SELECT 4078,'SEALING RING',8,2010,5,NULL,1,6


SELECT M.ID,(ROW_NUMBER() OVER(PARTITION BY M.ICODE ORDER BY M.ICODE)) 'RowNumber', M.ICODE, M.ITEM, M.MONTHS, M.YEARS, M.GRN, M.MRS, M.MRN, M.STOCK ,
(SELECT SUM((ISNULL(STOCK,0.0)+ISNULL(GRN,0.0)- ISNULL(MRS,0.0)+ ISNULL(MRN,0.0))) 
 FROM @Pindex WHERE ID<=M.ID AND ICODE=M.ICODE)
FROM @Pindex M
ORDER BY M.ICODE

Regards,
GVPrabu

Karruksen · Accepted Answer · 2013-03-25T19:46:00

SQL

DECLARE @pind TABLE (ID INT IDENTITY(1,1),ICODE INT, ITEM VARCHAR(100),
  MONTHS SMALLINT,YEARS SMALLINT,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,2,NULL,1,3 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,20,NULL,1,21 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,32,NULL,1,33 


SELECT M.ICODE, M.ITEM, M.MONTHS, M.YEARS, 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 CLOSEB
FROM @Pind M

IN SQL Server, First row Last Column is How to add with second row Columns?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0