PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Sunday, October 23, 2022

[FIXED] How to update USING sum and subquery

 October 23, 2022     nosql-aggregation, sql, sql-server, sql-subselect, sql-update     No comments   

Issue

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.


Solution

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


Answered By - DhruvJoshi
Answer Checked By - Cary Denson (PHPFixing Admin)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing