locked
Problem to fetch data using loop in SP RRS feed

  • 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

     

     

    • Moved by Alex Feng (SQL) Thursday, February 4, 2010 5:43 AM Moving to T-SQL forum for a quick answer (From:Getting started with SQL Server)
    Tuesday, February 2, 2010 7:00 AM

All replies

  • You can declare a cursor..
    Wednesday, February 3, 2010 9:38 AM
  • 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.
    Wednesday, February 3, 2010 11:15 AM
  • 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
    Wednesday, February 3, 2010 12:31 PM
  • 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
    
    Thursday, February 4, 2010 5:17 AM