SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE products
(
  product VARCHAR(255) PRIMARY KEY,
  quantity INT
);
INSERT INTO products
(product, quantity)
VALUES
('Product 1', 300),
('Product 2', 200),
('Product 3', 200),
('Product 4', 300);
4 rows affected

DECLARE @target DECIMAL(18,2) = 0.6;

WITH windowed AS 
(
  SELECT *, SUM(quantity) OVER () * 1.0 AS total 
  FROM products
)
SELECT *,
  CASE 
    WHEN (quantity / total) < @target 
    THEN ((@target * quantity) - quantity + (@target * (total - quantity))) / (1 - @target) 
  END AS needed
FROM windowed


product
quantity
total
needed


Product 1
300
1000.0
750.000000

Product 2
200
1000.0
1000.000000

Product 3
200
1000.0
1000.000000

Product 4
300
1000.0
750.000000


DECLARE @target DECIMAL(18,2) = 0.35;

WITH windowed AS 
(
  SELECT *, SUM(quantity) OVER () * 1.0 AS total 
  FROM products
)
SELECT 
  product
  quantity,
  total,
  quantity / total AS current_percent,
  @target AS target_percent,
  CASE 
    WHEN (quantity / total) < @target 
    THEN ((T * a) - a + (T * b)) / (1 - T)
  END AS needed
FROM windowed
  CROSS APPLY
  (
    SELECT 
      quantity AS a,
      total - quantity AS b,
      @target AS T
  ) eq


quantity
total
current_percent
target_percent
needed


Product 1
1000.0
0.30000000000000
0.35
76.923076

Product 2
1000.0
0.20000000000000
0.35
230.769230

Product 3
1000.0
0.20000000000000
0.35
230.769230

Product 4
1000.0
0.30000000000000
0.35
76.923076

product	quantity	total	needed
Product 1	300	1000.0	750.000000
Product 2	200	1000.0	1000.000000
Product 3	200	1000.0	1000.000000
Product 4	300	1000.0	750.000000

quantity	total	current_percent	target_percent	needed
Product 1	1000.0	0.30000000000000	0.35	76.923076
Product 2	1000.0	0.20000000000000	0.35	230.769230
Product 3	1000.0	0.20000000000000	0.35	230.769230
Product 4	1000.0	0.30000000000000	0.35	76.923076