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.