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?

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.

  • Sorry. Query is running in a SSIS package running on a SQL 2012 server. Data resides on separate SQL 2008 server. – Michael Richardson Oct 2 '15 at 16:14
  • 4
    What you may not be realising is that the p1.tank = 'B' or p1.tank = 'C' AND p1.scheduled_pull_date = s1.formatted_date condition works like this p1.tank = 'B' or (p1.tank = 'C' AND p1.scheduled_pull_date = s1.formatted_date) instead of like this (p1.tank = 'B' or p1.tank = 'C') AND p1.scheduled_pull_date = s1.formatted_date. (No idea if that's the culprit, just something that caught the eye.) – Andriy M Oct 2 '15 at 16:23
  • 2
    Your query is really ambiguous because you're using the same aliases for tables in the outer query and in subqueries, so that it's not clear if shadowing of outer aliases is intentional or casual. Moreover, it's not clear how to correlate rows from the outer query to rows from the subquery. As it's written now, subqueries are not correlated at all. I suggest that you post your question with tables DDL, some sample data and expected output based on that data. See spaghettidba.com/2015/04/24/… for guidance on how to provide this information – spaghettidba Oct 2 '15 at 16:23
  • @Andriy : Holy cripes! That is likely it. I will test that out. Write that up as an answer and, if that fixes everything, I'll mark it as the answer. – Michael Richardson Oct 2 '15 at 16:31
  • Actually absence of correlation between the subqueries and the outer query, as noted by @spaghettidba, may be one of the reasons too. – Andriy M Oct 2 '15 at 16:35

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:

  pOut.lead_ppm = (
      dbo.production AS p1
       (p1.tank = 'B' or p1.tank = 'C')
       AND p1.scheduled_pull_date = pOut.scheduled_pull_date
  pOut.sulfur_ppm = (
      dbo.production AS p2
      (p2.tank = 'B' or p2.tank = 'C')
      AND p2.scheduled_pull_date = pOut.scheduled_pull_date
  dbo.production AS pOut,
  dbo.assay_temp AS tOut
  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:

  dbo.production AS pOut
  INNER JOIN dbo.assay_temp AS tOut ON pOut.scheduled_pull_date = tOut.formatted_date
  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.

  • I think this did it. This bit of code was buried in a DTS package that has been running for years! I've been tasked to update it to SSIS and couldn't get it to do what I wanted it to do. That will teach me to assume that just because it is preexisting, it is actually working. – Michael Richardson Oct 2 '15 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.