DROP TABLE #TEST
DROP TABLE #TEMPTEST

GO

CREATE TABLE #TEST (ITEM VARCHAR(10),TOTAL INT, GRPO INT, REST INT)
CREATE TABLE #TEMPTEST (ID INT IDENTITY(1,1),ITEM VARCHAR(10),TOTAL INT, GRPO INT, REST INT, QSUM INT)

GO

INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,40)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,7)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,3)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM2',60,60)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM2',60,15)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM2',60,20)

INSERT INTO #TEMPTEST(ITEM,TOTAL,GRPO) VALUES ('ITEM3',10,5)
INSERT INTO #TEMPTEST(ITEM,TOTAL,GRPO) VALUES ('ITEM3',10,15)


GO

INSERT INTO #TEMPTEST(ITEM,TOTAL,GRPO)  
SELECT ITEM,TOTAL,GRPO FROM #TEST


GO


DECLARE @ID INT
DECLARE @COUNT INT
DECLARE @ITEM VARCHAR(20)
DECLARE @GRPO INT
DECLARE @TOTAL INT
DECLARE @SUM INT
SELECT @COUNT = COUNT(*) FROM #TEMPTEST
SET @ID =1

WHILE (@ID  <= @COUNT)
BEGIN
	SELECT @ITEM=ITEM, @GRPO=GRPO,@TOTAL=TOTAL FROM #TEMPTEST WHERE ID=@ID
	IF (@TOTAL > @GRPO)
		UPDATE #TEMPTEST SET REST = @GRPO WHERE ID=@ID
	ELSE
		BEGIN
			SELECT @SUM = SUM(GRPO) FROM #TEMPTEST WHERE ITEM=@ITEM AND TOTAL=@TOTAL AND GRPO<>@GRPO AND ID > @ID
			UPDATE #TEMPTEST SET REST = (TOTAL-@SUM) WHERE ID=@ID
		END	
	SET @ID = @ID + 1
END


GO

SELECT * FROM #TEMPTEST

INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,40)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,27)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,3)

quote:

---

Originally posted by khtan

i am still a bit lost on your requirement.

It seems that the ordering of your record is important ? Do you have another column that determine the sequence or ordering of the records ?

What happen if your data is like this ? What will be the expected result ?

INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,40)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,27)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,3)

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by abhit_kumar

INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,1,40)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,2,27)
INSERT INTO #TEST(ITEM,TOTAL,GRPO) VALUES ('ITEM1',28,3,3)

one modification in above insert stmt is i have one col of ID i.e. 1,2,3 so alyways first we have to compare with last ID i.e 3 with their qty and then ID 2 and ID 1.

Expected result should be like this:-
===========================================
0
25
3

---

SELECT t.ITEM,t.TOTAL,t.GRPO, 
CASE WHEN t.TOTAL > (t.GRPO +COALESCE( t1.Total,0)) THEN t.GPRO
ELSE  CASE WHEN SIGN((t.TOTAL-COALESCE( t1.Total,0)))> 0.0 THEN (t.TOTAL-COALESCE( t1.Total,0)) ELSE 0 END
END AS RestQty
FROM YourTable t
OUTER APPLY (SELECT SUM(GPRO) AS Total
             FROM YourTable
             WHERE Item= t.Item
             AND ID >t.ID)t1

quote:

---

Originally posted by abhit_kumar

thanks alot visakh...its working now..
sorry i confused alot and thanks to all of them who shared there opinion...

---

quote:

---

Originally posted by abhit_kumar

Hello Visakh,

Thanx for the valuable suggestion.


I have tested your given code, its working good, except its
failing in one case where the GRPOid is duplicating for particular item.

ID ITEM TOTALQTY GRPOID GRPOQTY RESTQTY
50724 ZBR-R1 26 972 20 20
50725 ZBR-R1 26 972 30 26

Note :- First column ID is IDENTITY column

I have used GRPOID column for the condition in Outer Apply subquery like GRPOID>t.GRPOID

Look the output, for particular item there are two GRPO ID, item with different quantity.
because there is two times entry of the item in GRPO with different GRPO qty.

The entry has been done by end user in the software, so we cant modify it.

please guide me.

---