What is the most compact way to calculate and then Update a maximum value for each day's data?

Question

I have a database that stores records for each bundle of material. A sample is taken from each bundle and sent out for assaying (for lead and sulfur). I receive the assay data and write the assay value to the record. I also take the maximum value for each assays and assign them to the day's "sample" record.

Generally, I only receive a single days assays at one time. However, on occasion, I received the assays for multiple days (if there was a re-assay or assays were delayed). When this happens, I end up assigning the maximum for all included days instead of for each day separately.

What is a better way to set up the following query to prevent one day's assays from being included as a maximum value for another day?

UPDATE pOut
SET
pOut.lead_ppm = (SELECT MAX(p1.lead_ppm)
 FROM production p1,  assay_temp t1
 WHERE (p1.tank = 'B' or p1.tank = 'C')
 AND p1.scheduled_pull_date = t1.formatted_date),
pOut.sulfur_ppm = (SELECT MAX(p2.sulfur_ppm)
 FROM production p2,  assay_temp t2
 WHERE (p2.tank = 'B' or p2.tank = 'C')
 AND p2.scheduled_pull_date = t2.formatted_date) 
FROM production pOut, assay_temp tOut
WHERE pOut.tank = 'S'
AND pOut.cell_num = 1
AND pOut.scheduled_pull_date = tOut.formatted_date

Code has been updated to use differently named aliases and to include the needed parenthesis. I believe now I will receive a single maximum for lead and for sulfur, likely from the earliest date. I need to improve this so that I get a maximum value for each day with assays, and write that value to the proper day's "sample" record.

Answer 1

I am not entirely sure but if the aggregated results are supposed to be per date and the date is production.scheduled_pull_date, then perhaps this:

UPDATE
  pOut
SET
  pOut.lead_ppm = (
    SELECT
      MAX(p1.lead_ppm)
    FROM
      dbo.production AS p1
    WHERE
       (p1.tank = 'B' or p1.tank = 'C')
       AND p1.scheduled_pull_date = pOut.scheduled_pull_date
  ),
  pOut.sulfur_ppm = (
    SELECT
      MAX(p2.sulfur_ppm)
    FROM
      dbo.production AS p2
    WHERE
      (p2.tank = 'B' or p2.tank = 'C')
      AND p2.scheduled_pull_date = pOut.scheduled_pull_date
  )
FROM
  dbo.production AS pOut,
  dbo.assay_temp AS tOut
WHERE
  pOut.tank = 'S'
  AND pOut.cell_num = 1
  AND pOut.scheduled_pull_date = tOut.formatted_date
;

Although I would also rewrite the join using the contemporary syntax:

FROM
  dbo.production AS pOut
  INNER JOIN dbo.assay_temp AS tOut ON pOut.scheduled_pull_date = tOut.formatted_date
WHERE
  pOut.tank = 'S'
  AND pOut.cell_num = 1

The assay_temp doesn't seem needed in the subqueries at all, and instead of matching scheduled_pull_date against formatted_date the subqueries should correlate with the outer query. Since you want aggregations per date, it seemed to me the subqueries should match against pOut.scheduled_pull_date.