Updating values in one row from values in another row

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.

Answer 1

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.

 

 

 

Answer 2

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