Mysql Nested sub query
Dear all
I am new to this forum and need some help with what i would think to be a nested query.
Basically i need a query for a stock take that returns stock on hand as at a given date.
This query also needs to return the last GRV price in relation to the given date
and if possible, weighted average and max price also in relation to the same given date.
query should return something like this:
part_code
part_description
unit_of_measure
stock_on_hand_as_at_a_specific_date
latest_grv_price_prior_to_the_specified_date
weighted_average_cost_up_to_specified_date
maximum_cost_price_up_to_specified_date
I am able to achieve the stock on hand with the following query:
select
`parts`.`part_code` AS `part_code`,
`parts`.`part_description` AS `part_description`,
`parts`.`unit_of_measure` AS `unit_of_measure`,
`warehouses`.`warehouse_name` AS `warehouse_name`,
sum((`document_header`.`multiplier` * `document_transactions`.`quantity`)) AS `stock_on_hand`
from
(((`parts` join `document_transactions` on((`parts`.`part_code` = `document_transactions`.`part_code`))) join `document_header` on((`document_transactions`.`document_number` = `document_header`.`document_number`))) join `warehouses` on((`document_transactions`.`warehouse_id` = `warehouses`.`warehouse_id`)))
where
(`document_header`.`document_date` < _latin1'2022-02-25')
group by
`parts`.`part_code`,`document_transactions`.`warehouse_id`;
I am then having to itterate through the above record set and one by one find the last GRV price for each item.
I am sure that there must be a more efficient way ??
Here are the tables and some sample data
CREATE TABLE `document_header` (
`document_number` char(12) NOT NULL,
`document_date` date default NULL,
`document_type_id` tinyint(4) default NULL,
`multiplier` tinyint(4) default '-1',
PRIMARY KEY (`document_number`),
UNIQUE KEY `document_number` (`document_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `parts` (
`part_code` varchar(12) NOT NULL,
`part_description` varchar(30) default NULL,
`unit_of_measure` varchar(5) default 'Each',
PRIMARY KEY (`part_code`),
UNIQUE KEY `part_code` (`part_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `warehouses` (
`warehouse_id` int(11) NOT NULL auto_increment,
`warehouse_name` varchar(30) default NULL,
PRIMARY KEY (`warehouse_id`),
UNIQUE KEY `warehouse_id` (`warehouse_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE `document_transactions` (
`document_transaction_id` int(11) NOT NULL auto_increment,
`document_number` char(12) default NULL,
`warehouse_id` int(11) default NULL,
`part_code` varchar(12) default NULL,
`usd_price` decimal(9,2) default NULL,
`exchange_rate` decimal(11,5) default NULL,
`quantity` decimal(6,1) default NULL,
PRIMARY KEY (`document_transaction_id`),
UNIQUE KEY `document_transaction_id` (`document_transaction_id`),
KEY `document_number` (`document_number`),
KEY `part_code` (`part_code`),
KEY `warehouse_id` (`warehouse_id`),
CONSTRAINT `document_transactions_fk2` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`warehouse_id`) ON UPDATE CASCADE,
CONSTRAINT `document_transactions_fk` FOREIGN KEY (`document_number`) REFERENCES `document_header` (`document_number`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `document_transactions_fk1` FOREIGN KEY (`part_code`) REFERENCES `parts` (`part_code`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
INSERT INTO `document_header` (`document_number`, `document_date`, `document_type_id`, `multiplier`) VALUES
('GRVBA0000001','2022-01-05',1,1),
('GRVBA0000002','2022-01-06',1,1),
('GRVBA0000003','2022-01-14',1,1),
('GRVBA0000004','2022-02-01',1,1),
('GRVBA0000005','2022-02-10',1,1),
('ISSBA0000001','2022-01-07',6,-1),
('ISSBA0000002','2022-01-08',6,-1),
('ISSBA0000003','2022-01-22',6,-1),
('ISSBA0000004','2022-01-23',6,-1),
('ISSBA0000005','2022-02-03',6,-1);
COMMIT;
INSERT INTO `warehouses` (`warehouse_id`, `warehouse_name`) VALUES
(1,'Main Stores'),
(2,'Midlands Branch Store'),
(3,'Head Office');
COMMIT;
INSERT INTO `parts` (`part_code`, `part_description`, `unit_of_measure`) VALUES
('ABC001','Part ABC Description','Each'),
('DEF001','Part DEF Description','Each'),
('GHI001','Part GHI Description','Each');
COMMIT;
INSERT INTO `document_transactions` (`document_transaction_id`, `document_number`, `warehouse_id`, `part_code`, `usd_price`, `exchange_rate`, `quantity`) VALUES
(3,'GRVBA0000001',1,'ABC001',9.45,1.41000,100.0),
(4,'GRVBA0000002',1,'DEF001',50.36,1.41000,24.0),
(5,'ISSBA0000001',1,'ABC001',9.45,1.41000,6.0),
(6,'ISSBA0000002',1,'ABC001',9.45,1.41000,6.0),
(7,'GRVBA0000003',1,'GHI001',22.45,1.45000,36.0),
(8,'ISSBA0000003',1,'DEF001',50.36,1.48000,8.0),
(9,'ISSBA0000004',1,'GHI001',22.45,1.45000,11.0),
(10,'GRVBA0000004',1,'ABC001',9.85,1.48000,50.0),
(11,'ISSBA0000005',1,'ABC001',9.85,1.48000,38.0),
(12,'GRVBA0000005',1,'ABC001',8.25,1.44000,2000.0);
COMMIT;
Any assistance would be greatly appreciated.
Regards
Ken