join and calculation issues

Question

Alright, after 3-4 days of trying to solve this I am giving up. I started out with only one table but now I have changed it alot so I am able to have two tables, I thought it would make it alot easier for a rookie like me. See my earlier post here

Instead of posting all the table details here I am providing a screenshot: Tables and expected result

I have managed to come quite far with this but the problem is that I am not able to do the calculations excluding the batches that should not be included.

How could I solve this? I really appreciate the help I get from you guys. Thanks in advance

Answer 1

First step: Create a filter for the details table

SELECT
  MAX(id) AS id
FROM details
GROUP BY `concat`, `batch`

Next step: Use this to query the details table

SELECT * FROM details
WHERE id IN (
    SELECT
      MAX(id) AS id
    FROM details
    GROUP BY `concat`, `batch`
)

Next step: Use this derived table to join the master table for your final result

SELECT
  `master`.id AS id,
  `master`.plant AS plant,
  `master`.`code` AS `code`,
  COUNT(*) AS distinct_batches,
  SUM(filtereddetails.volume) AS total_vol,
  SUM(filtereddetails.`value`) AS total_val,
  SUM(filtereddetails.volume*filtereddetails.risk) AS risk_vol,
  SUM(filtereddetails.`value`*filtereddetails.risk) AS risk_val,
  MAX(filtereddetails.end_date-filtereddetails.start_date) AS max_date_diff
FROM
  `master`
  INNER JOIN (
    SELECT * FROM details
    WHERE id IN (
        SELECT
          MAX(id) AS id
        FROM details
        GROUP BY `concat`, `batch`
    )
  ) AS filtereddetails ON `master`.`concat`=filtereddetails.`concat`
GROUP BY
  `master`.`concat`