I'm trying to turn this procedure
into function
, so I can use it inside another procedure
, but I can not evolve:
CREATE PROCEDURE bancodados.sp_calculo(param_id_cnpj varchar(14))
BEGIN
SELECT id_cnpj,
AVG(valor)*12 AS fat,
regime,
aliquota,
(aliquota*SUM(valor))/100 AS pagamento,
fun_dia_pagamento(fun_data_pagamento(NOW()))
AS data FROM (
SELECT
a.valornf,
a.id_cnpj,
b. regime,
c. minimo,
c. maximo,
c. aliquota
FROM bancodados.tabela_valores a
LEFT JOIN bancodados.tabela_empresa b
ON a.id_cnpj = b.id_cnpj
LEFT JOIN bancodados.tabela_anexos c
ON b.regime = c.regime AND
a.valor >= c.minimo AND
a.valor <= c.maximo
WHERE (a.id_cnpj = param_id_cnpj AND
DATE_FORMAT(data_registro,'%Y-%m') < DATE_FORMAT(fun_mes_anterior(NOW()),'%Y-%m')
AND
DATE_FORMAT(data_registro,'%Y-%m') >= DATE_FORMAT(fun_12meses_antes(NOW()),'%Y-%m')
))x;
END $$