Click here to Skip to main content
15,849,616 members
Please Sign up or sign in to vote.
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
Updated 25-Mar-13 8:52am
v4
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)

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
 
Share this answer
 
v2
Comments
Maciej Los 25-Mar-13 13:55pm    
It could be it!!! ;)
+5!
Karruksen 26-Mar-13 1:06am    
gvprabu sir, Really i am satisfied by means of your answer...Thank you very much sir...
gvprabu 26-Mar-13 1:20am    
Hi Karrulsen...
Check with all possible Sample Inputs ...
Row_Number is There because of Item Data Order...
always welcome... cal me as Prabu... Sir is not at all required...
:-) :-)
Karruksen 26-Mar-13 1:29am    
Hai prabu...
query was sucessfully executed...

i want more explaination about that query.. i think , first you creat one temp table with unique identity,then some data inserted finally i confused from select query..pls explain final select query..
gvprabu 26-Mar-13 1:33am    
Hi,
If your Table have any one column having Unique values like 'ID' then use that Column.
If u don't have any ID Column then Create Temp table or Table variable like as follows with one Identity Column (ID)
-- Table variable Script
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))

U need to loop for SUM the values of above rows... thats why we need one Unique value for Each row... Row_Number is not required I think... with out that also can u try... If data is not coming with proper order then add the row number.
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
 
Share this answer
 
v3

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