How to update USING sum and subquery

Question

I have a SQL Server table similar to this:

InkitemNo	CapacityUnit	NewInk	OldInk	ReturnInk	ProdQty	Description	UsedInk
204	Machine1	5	2	0	4000	Next	?
223	machine2	4	3	1	8000	NULL	?
204	Machine2	0	0	0	5000	Next	?
224	Machine2	4	0	2	3000	Next	?

I'm trying to write a query with this formula:

Example to get 1 row used ink

(5 + 2 -2 )* 4000/ 12000 = 1,67

to get 2 row used ink

(4 + 3 - 1) = 6

to get 3 row usedink

(5 + 2 - 2) * 5000 / 12000 = 2,08

to get 4 row usedink

(5 + 2 - 2) * 3000 / 12000 = 1,25

(NewInk + OldInk - ReturnInk) * ProdQty / Sum(ProdQty)

This formula used when the criteria is

• CapacityUnit & InkItemNo is same
• Description is not NULL

To get the result of used ink, I used this query

update InkEstimationSave =

(NewInk + OldInk - ReturnInk) * ProdQty / Sum(ProdQty]

but it does not work.

Answer 1

Based on your logic query you are looking for is

fiddle link

    ;with cte as 
(
select *, SUM(ProdQty) OVER (partition by InkItemNo, Capacityunit) as denom 
from yourtable
)

update  cte
set UsedInk =
           (newInk+OldInk - ReturnInk) * ProdQty
          /denom
         *1.00 
where Description is NOT NULL

select * from Yourtable