嗨朋友，



检查我的解决方案......在你的表中，你有唯一的ID列表示使用以下解决方案。

否则使用ID创建临时表（IDENTITY）列并使用相同的解决方案。

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.

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

Regards,
GVPrabu

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