DROP FUNCTION IF EXISTS func_Period_OVL_TimekeepTractions_value;

DELIMITER $$

CREATE FUNCTION func_Period_OVL_TimekeepTractions_value(
	p_date1_begin    VARCHAR(20),
	p_date1_end      VARCHAR(20),
	p_transport_type VARCHAR(10)
)
RETURNS REAL
BEGIN

SET 
	@d_start1 = p_date1_begin,
	@d_end1   = p_date1_end;
	
DO 
	@v1 := IFNULL((
		SELECT SUM(vw.IS_DELAY)
		FROM rpt_qualitydaily_vw_all_transports vw
		WHERE
			    vw.transport_date BETWEEN @d_start1 AND @d_end1
			AND vw.transport_type = p_transport_type
			AND vw.hour_real IS NOT NULL
	),0),
	@v2 := IFNULL((
		SELECT SUM(vw.IS_TRANSPORT)
		FROM rpt_qualitydaily_vw_all_transports vw
		WHERE
			    vw.transport_date BETWEEN @d_start1 AND @d_end1
			AND vw.transport_type = p_transport_type
			AND vw.hour_real IS NOT NULL
	),0),
	@r := (
		SELECT IF(@v2 = 0, 0, @v2 / (@v2 + @v1))
	)
	;
	
RETURN @r;

END
$$

DELIMITER ;