locked
Updating values in one row from values in another row RRS feed

  • Question

  • Hi

     

    I have a table containing:

     

    ProductCat     Product       Qty       NewQty

    TypeA             ProductA      -10

    TypeA             ProductB        5

    TypeB             ProductX         8

    TypeB            ProductY         -5

     

    I need to get rid of negative values in the same ProductCat by adding surplus values to the negative values in the same ProductCat ie. This is what I ant the table to look like. 

     

    ProductCat     Product       Qty       NewQty

    TypeA             ProductA      -10         -5       (Add ProductB to ProductA in the same ProductCat  to reduce the deficit)

    TypeA             ProductB        5           0       (Due to the fact that I added 5 to ProductA NewQty must be reduced by 5)

    TypeB             ProductX         8           3

    TypeB            ProductY         -5           0      (Deficit only has to be reduced to 0 where possible therefor the balance

                                                                       of 3 for ProductX)

     

    Note the values in Qty can stay as the same. I am interested in the New values.

    Could someone please help me with the correct method of doing this?

     

    Thank you.

    Saturday, October 13, 2007 12:46 PM

All replies

  • Something like this: (UNTESTED)

     

    SET NOCOUNT ON

    Code Block

     

    DECLARE @MyTable table
       (  ProductCat    varchar(10),
          Product       varchar(20),
          Qty           int,
          NewQty        int
       )

     

    INSERT INTO @MyTable VALUES ( 'TypeA', 'ProductA', -10, NULL )
    INSERT INTO @MyTable VALUES ( 'TypeA', 'ProductB', 5, NULL )
    INSERT INTO @MyTable VALUES ( 'TypeB', 'ProductX', 8, NULL )
    INSERT INTO @MyTable VALUES ( 'TypeB', 'ProductY', -5, NULL )

     

    UPDATE @MyTable
       SET NewQty = CASE
                       WHEN Qty < 0 THEN t2.NewQty
                       ELSE 0
                    END
       FROM @MyTable t1
          JOIN ( SELECT
                    ProductCat,
                    NewQty = sum( Qty )
                 FROM @MyTable
                 GROUP BY ProductCat
               ) t2
              ON t1.ProductCat = t2.ProductCat

     

    SELECT *
    FROM @MyTable

     

    ProductCat Product              Qty         NewQty     
    ---------- -------------------- ----------- -----------
    TypeA      ProductA             -10         -5
    TypeA      ProductB             5           0
    TypeB      ProductX             8           0
    TypeB      ProductY             -5          3

     

     

    This differs from your 'desired results' slightly since your stated 'rule' was to correct the entries with a negative balance.

    In your desired results, you corrected a positive value.

     

     

     

    Saturday, October 13, 2007 3:30 PM
  • Thank you Arnie, your assistance is appreciated. It works superbly!

     

    You are correct when you say I have corrected the positive value. I overlooked that.

     

    The objective is to get the negative values to 0. This would mean subtraction from the positive values in the category which means the positive values would need to change accordingly.

     

    Your app does this perfectly, however once I reach 0 the values for the positive need to remain and not added to the negative as you can see below

     

     

    DECLARE @MyTable table
       (  ProductCat    varchar(10),
          Product       varchar(20),
          Qty           int,
          NewQty        int
       )

     

    INSERT INTO @MyTable VALUES ( 'TypeA', 'ProductA', -10, NULL )
    INSERT INTO @MyTable VALUES ( 'TypeA', 'ProductB', 5, NULL )
    INSERT INTO @MyTable VALUES ( 'TypeA', 'ProductC', 5, NULL )
    INSERT INTO @MyTable VALUES ( 'TypeA', 'ProductD', 5, NULL )
    INSERT INTO @MyTable VALUES ( 'TypeB', 'ProductX', 8, NULL )
    INSERT INTO @MyTable VALUES ( 'TypeB', 'ProductY', -5, NULL )

     

    UPDATE @MyTable
       SET NewQty = CASE
                       WHEN Qty < 0 THEN t2.NewQty
                       ELSE 0
                    END
       FROM @MyTable t1
          JOIN ( SELECT
                    ProductCat,
                    NewQty = sum( Qty )
                 FROM @MyTable
                 GROUP BY ProductCat
               ) t2
              ON t1.ProductCat = t2.ProductCat

     

    SELECT *
    FROM @MyTable

    This is the result

    TypeA  ProductA  -10  5
    TypeA  ProductB     5  0
    TypeA  ProductC    5  0
    TypeA  ProductD    5  0
    TypeB  ProductX     8  0
    TypeB  ProductY   -5  3

     

    What I need is this......

     

    TypeA  ProductA  -10  0
    TypeA  ProductB     5  0
    TypeA  ProductC    5  0
    TypeA  ProductD    5  5

    TypeB  ProductX     8  3

    TypeB  ProductY   -5  0

    You can see ProductD remains the same as the desired result of get ProductA to 0 was accomplished with ProductB and ProductC.

    Another example: ProductX should be 3 not ProductY

     

    Regards

     

    Saturday, October 13, 2007 5:04 PM