CODEWITHSUNDEEP
sql-server
Khalil
Khalil

Reputation: 119

SQL Update Query from sum and first row of another table

I am creating the SQL query which will update Quantities of 'Table A' from from 'Table B'. However couple of fields in the Table B are not to be sum and their latest value (First or Last row value based on Date Order) is also to be updated in the 'Table A'. Below is the query sofar.

UPDATE r
SET r.Completed = t.MoveQty, r.Scrapped = t.ScrapQty, r.ReWork = t.ReworkQty
FROM JobRoutings r
INNER JOIN
(
  SELECT JobID, ScanFromProcessID, SUM(ScanMoveQty) MoveQty, Sum(ScanScrapQty) ScrapQty, Sum(ScanRecutQty) ReworkQty
  FROM JobTrans
  GROUP BY  JobID, ScanFromProcessID
) t ON r.JobID = t.JobID AND r.ProcessID  = t.ScanFromProcessID;

Fields which are needed to be included in the above query are:

Latest value of [JobTrans].ScannedDate and [JobTrans].PC [ORDER BY ScannedDate] need to update [JobRoutings].LastScanDate and [JobRoutings].PC

Upvotes: 0

Views: 194

Answers (1)

Max Szczurek
Max Szczurek

Reputation: 4334

Here's a way that joins a 2nd subquery and gets the latest PC/ScannedDate values using ROW_NUMBER():

UPDATE r
SET r.Completed = t.MoveQty, r.Scrapped = t.ScrapQty, r.ReWork = t.ReworkQty, r.LastScanDate = t.ScannedDate, r.PC = t.PC
FROM JobRoutings r
INNER JOIN
(
  SELECT JobTrans.JobID, JobTrans.ScanFromProcessID, t2.ScannedDate, t2.PC, SUM(ScanMoveQty) MoveQty, Sum(ScanScrapQty) ScrapQty, Sum(ScanRecutQty) ReworkQty
  FROM JobTrans 
  INNER JOIN
  (
      SELECT JobID, ScanFromProcessID, ScannedDate, PC, ROW_NUMBER() OVER (PARTITION BY JobID, ScanFromProcessID ORDER BY ScannedDate DESC) AS rn
      FROM JobTrans
  )t2 ON JobTrans.JobID = t2.JobID AND JobTrans.ScanFromProcessID = t2.ScanFromProcessID AND t2.rn = 1
  GROUP BY  JobTrans.JobID, JobTrans.ScanFromProcessID, t2.ScannedDate, t2.PC
) t ON r.JobID = t.JobID AND r.ProcessID  = t.ScanFromProcessID

Upvotes: 1

Related Questions