Problem to fetch data using loop in SP

Question

Hello Friends,

I have a question please help me.

currently this scenario is working for me.

I have displayed the data based upon item description.

Item Desc       TotalQty
Item A               100            ID     Qty       Rest Qty
                                          10      40         ?
                                          20      80         ?

Im facing problem to retriveing data for Rest Qty coloumn.
I have created stored procedure to do this stuff.

What i need suppose Total qty < Last ID(i.e 20) Qty(80) then it should simply display 80 t0 the next column, then it will look for previous ID(i.e. 10) Qty(40),
here in that case Qty is 40 but in total of both ID (i.e. 20 and 10) Quantity(i.e. 80 and 40) is 120 which is more then Total qty(i.e. 100), hence in that case it should subtract Total qty(100) - Qty(80) = 20 so it should dispaly 20 next to ID(10) column of Rest Qty.

Means evertime Total qty should compare with both the IDs Qty(startting with top most ID)
and see whether its greater or not and display it to next col of Rest Qty.

First Scenario
===============
1) 100 > 80 hence 80 comes to Rest Qty
2) 100 > 40(but since 80+40 which is more then 100 hence in that case its should be 100-80 = 20 should comes next to the coloumn)

I need to do this by using loop condition in stored procedure, i have
inserted all data in temp table from main table.

Please guide me, its very urgent.

Regards,
Av

 

 

Answer 1

You can declare a cursor..

Answer 2

Hi,

I am a little bit confused, could you post the data table structure here?

---

Chunsong Feng
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Hi  Abhit!!!

             See I have prepared one stored procedure for you, see if this can help you...

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <JAYESH UPADHYAY>
-- Create date: <20100203>
-- Description:    <Description,,>
-- =============================================
ALTER procedure test
AS
BEGIN

    DECLARE @Id INT
    DECLARE @TotalQty INT
    DECLARE @Qty INT
    DECLARE @RestQty INT
    DECLARE @Id1 INT
    DECLARE @TotalQty1 INT
    DECLARE @Qty1 INT
    DECLARE @RestQty1 INT
    DECLARE @temp INT

    select Id,TotalQty,Qty,RestQty into ##temptable from abc
    select Id,TotalQty,Qty,RestQty into ##temptable1 from abc

    DECLARE curTempUpdate CURSOR FOR
        SELECT Id,TotalQty,Qty,RestQty from ##temptable
    DECLARE curTempUpdate1 CURSOR FOR
        SELECT Id,TotalQty,Qty,RestQty from ##temptable1
        OPEN curTempUpdate
        OPEN curTempUpdate1       
            FETCH Next FROM curTempUpdate into @Id , @TotalQty , @Qty , @RestQty
            FETCH Next FROM curTempUpdate1 into @Id1 , @TotalQty1 , @Qty1 , @RestQty1
            FETCH Next FROM curTempUpdate1 into @Id1 , @TotalQty1 , @Qty1 , @RestQty1
        WHILE @@FETCH_STATUS = 0
        BEGIN
            if((@Qty + @Qty1)> @TotalQty)
            BEGIN
                SET @temp = @TotalQty - @Qty1
                update abc set RestQty=@temp where Id=@Id
            END
            FETCH Next FROM curTempUpdate into @Id , @TotalQty , @Qty , @RestQty
            FETCH Next FROM curTempUpdate1 into @Id1 , @TotalQty1 , @Qty1 , @RestQty1
        END--while
        drop table ##temptable
        drop table ##temptable1
        Deallocate curTempUpdate
        Deallocate curTempUpdate1
END

--EXEC test

Answer 4

Hey jayesh and all others,

thanks for giving time to my post.

But i dont want to use cursor...Look at the below code and ran it, this is what i m expectng, but its not work for item 3.

Hi frnds,

Please see below code its working for ITEM 1 and ITEM 2, but fails for ITEM 3.

Total quantity should compare with last quanity first and then before that per item wise.

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