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
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`