MYSQL calculate volume from raw material units on raw materials in stock-CodePudding

I'm trying to figure out the calculation for finding the stock required and also display the volume output if it cant reach the max tresshold in this case volume '1000'.

I will try my best to explain it as plain as possible. (tables and columns are in dutch)

Example:

To produce a volume of 1000 I need multiple raw materials. The raw materials have different totals to go in the 'cooking pot' (and to reach 1000, aqua is added but thats a side note).

1000 is the base for calculating the numbers in the table receptgrondstoffen

First I have the table with the recipe called 'naam' (name)

table: recepten

CREATE TABLE `recepten` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `administratieid` int(11) NOT NULL DEFAULT 0,
  `omzetgroepid` int(11) NOT NULL DEFAULT 0,
  `artikelgroepid` int(11) NOT NULL DEFAULT 0,
  `artikelnummer` int(11) NOT NULL DEFAULT 0 COMMENT 'gevuld vanuit snelstart',
  `factornummer` varchar(20) NOT NULL,
  `eannummer` varchar(20) NOT NULL,
  `naam` varchar(255) NOT NULL,
  `notitie` mediumtext NOT NULL,
  `volume` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `onderzoek` int(1) NOT NULL DEFAULT 0,
  `viscositeit` varchar(50) NOT NULL,
  `phwaarde` varchar(50) NOT NULL,
  `dichtheid` varchar(50) NOT NULL,
  `thtmaanden` int(11) NOT NULL DEFAULT 0,
  `voorraadcontrole` int(1) NOT NULL DEFAULT 0,
  `drempelwaarde` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `recepten` (`id`,`administratieid`,`omzetgroepid`,`artikelgroepid`,`artikelnummer`,`factornummer`,`eannummer`,`naam`,`notitie`,`volume`,`onderzoek`,`viscositeit`,`phwaarde`,`dichtheid`,`thtmaanden`,`voorraadcontrole`,`drempelwaarde`) VALUES (1,0,0,702,300001,'122','','test','test',1000.00000,1,'1','2','3',36,0,1);
INSERT INTO `recepten` (`id`,`administratieid`,`omzetgroepid`,`artikelgroepid`,`artikelnummer`,`factornummer`,`eannummer`,`naam`,`notitie`,`volume`,`onderzoek`,`viscositeit`,`phwaarde`,`dichtheid`,`thtmaanden`,`voorraadcontrole`,`drempelwaarde`) VALUES (2,0,0,704,300002,'1234','','test1','test',1000.00000,1,'1','2','3',36,0,100);

Second tables are the recipe items that go into the 'cooking pot'. There are 2 raw material lines. Both have a number of 100 so for each volume of 1000, 100 units from both are required. If I change the volume to 100 then 10 units from both are required.

table: receptgrondstoffen

CREATE TABLE `receptgrondstoffen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receptid` int(11) NOT NULL DEFAULT 0,
  `grondstofid` int(11) NOT NULL DEFAULT 0,
  `aantal` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `percentage` decimal(10,5) NOT NULL DEFAULT 0.00000,
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `receptgrondstoffen` (`id`,`receptid`,`grondstofid`,`aantal`,`percentage`) VALUES (2,1,1,100.00000,10.00000);
INSERT INTO `receptgrondstoffen` (`id`,`receptid`,`grondstofid`,`aantal`,`percentage`) VALUES (3,1,2,100.00000,10.00000);

The 'grondstofbatch' tables is the raw material quantity that's been bought

Table: grondstofbatch

CREATE TABLE `grondstofbatch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grondstofid` int(11) NOT NULL DEFAULT 0,
  `leveranciersid` int(11) NOT NULL DEFAULT 0,
  `batchnummer` varchar(50) NOT NULL,
  `datum` int(10) NOT NULL DEFAULT 0,
  `thtdatum` int(10) NOT NULL DEFAULT 0,
  `voorraad` int(11) NOT NULL DEFAULT 0,
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `grondstofbatch` (`id`,`grondstofid`,`leveranciersid`,`batchnummer`,`datum`,`thtdatum`,`voorraad`) VALUES (1,1,4,'1224-4',1662626077,1665266400,100);
INSERT INTO `grondstofbatch` (`id`,`grondstofid`,`leveranciersid`,`batchnummer`,`datum`,`thtdatum`,`voorraad`) VALUES (2,1,3,'#34423',1662626904,1663970400,300);
INSERT INTO `grondstofbatch` (`id`,`grondstofid`,`leveranciersid`,`batchnummer`,`datum`,`thtdatum`,`voorraad`) VALUES (3,2,3,'#00931',1662626904,1663970400,200);

Volume is the kicker.

What i want, if I use volume amount lets say in this case its 3000 I get a return that the max volume be created is X because not all raw materials are present.

The base is 1000 = 100 so for 3000 it is 300 and only 1 raw material has the required stock. So that means it will be max 2000 volume. And if there is no volume that can be produced then 0.

results;

CREATE TABLE `results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `volume` int(11) NOT NULL,
  `quantity_needed` mediumtext NOT NULL,
  `stock` mediumtext NOT NULL,
  `result` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (1,1000,'100,100','400,200',1000);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (2,400,'100,100','400,200',2000);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (3,3000,'100,100','400,200',2000);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (4,500,'100,100','400,200',500);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (5,500,'50,75','400,200',500);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (6,500,'50,75','25,75',250);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (7,500,'30,30','25,75',416);

Hope there is a SQL wizard that can help me out.

CodePudding user response：

I think you are looking for something like

SELECT r.id, 
 r.volume,
 group_concat(rg.aantal ORDER BY rg.id SEPARATOR ',') quantity_needed,
 group_concat(g.voorraad ORDER BY rg.id SEPARATOR ',') stock, 
 min(floor(g.voorraad/rg.aantal)) * r.volume result
FROM recepten r INNER JOIN receptgrondstoffen rg
  on r.id = rg.receptid
 inner join grondstofbatch g
  on g.id = rg.grondstofid
GROUP BY r.id, r.volume

You can see it with your sample data in this fiddle.

I am possibly misunderstanding your problem since your result data doesn't seem to match your sample data. If so, please help me understand what is wrong with this solution and I will try to fix it.

The idea here is that, for each recepten, calculate min(floor(grondstofbatch.voorraad/receptgrondstoffen.aantal)). Floor since we want an integer (e.g. if we need 100 units for the recipe and actually have 250 units, floor(250/100) = 2 possible batches). Min since we want the limiting factor (e.g. it doesn't matter if one item in the recipe has enough for 20 batches if another item only has enough for 1 batch).

Hopefully this is in the ballpark of what you're looking for.

Edited: To handle the case where to sum the available quantities I have changed the alias g to be a subquery using the grondstofid table (rather than just the straight grondstofid table). I also realize I was likely joining the grondstofid table incorrectly above (g.id = rg.grondstofid rather than g.grondstofid = rg.grondstofid).

SELECT r.id, 
 r.volume,
 group_concat(rg.aantal ORDER BY rg.id SEPARATOR ',') quantity_needed,
 group_concat(g.voorraad ORDER BY rg.id SEPARATOR ',') stock, 
  min(floor(g.voorraad/rg.aantal)) * r.volume result
FROM recepten r INNER JOIN receptgrondstoffen rg
  on r.id = rg.receptid
 inner join (SELECT grondstofid, sum(voorraad) voorraad FROM grondstofbatch GROUP BY grondstofid) g
  on g.grondstofid = rg.grondstofid
GROUP BY r.id, r.volume

Please take a look at a fiddle of this version