23 lines
551 B
SQL
23 lines
551 B
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`ticket_CC_volume`(vTicketFk INT)
|
|
RETURNS decimal(10,1)
|
|
NOT DETERMINISTIC
|
|
READS SQL DATA
|
|
BEGIN
|
|
|
|
DECLARE vCC DECIMAL(10,1);
|
|
|
|
SELECT sum(sv.volume * IF(i.itemPackingTypeFk = 'H', vc.dutchCompressionRate, 1)) / (p.volume / 1000000)
|
|
INTO vCC
|
|
FROM vn.saleVolume sv
|
|
JOIN vn.sale s ON s.id = sv.saleFk
|
|
JOIN vn.item i ON i.id = s.itemFk
|
|
JOIN vn.packaging p ON p.id = 'CC'
|
|
JOIN vn.volumeConfig vc
|
|
WHERE sv.ticketFk = vTicketFk;
|
|
|
|
RETURN vCC;
|
|
|
|
END$$
|
|
DELIMITER ;
|