IF EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UFD_TELE_PAINEL_ETAPAS_TEMP]') AND TYPE IN (N'FN',N'IF',N'TF',N'FS',N'FT') )
BEGIN
	DROP FUNCTION UFD_TELE_PAINEL_ETAPAS_TEMP
END
go 
 
CREATE FUNCTION [DBO].[UFD_TELE_PAINEL_ETAPAS_TEMP] (@CD_FILIAL INT)
	RETURNS @RS_RET TABLE (
		 CD_ETAPAS INT
		,TIPO INT
		,QTD INT )
AS
BEGIN
	INSERT INTO @RS_RET
	SELECT CD_ETAPAS
		  ,TIPO
		  ,COUNT(TIPO) AS QTD
	FROM (SELECT PEDIDO_ETAPA.CD_ETAPAS
				,(CASE	WHEN PEDIDO_ETAPA.PRAZO > 2 THEN 1
						WHEN PEDIDO_ETAPA.PRAZO < 0 THEN 3
						ELSE 2
				   END ) AS TIPO
			FROM ( SELECT  2 AS CD_ETAPAS
						  ,DATEDIFF(MINUTE, GETDATE(), A.ENT_DTHR) - D.SEPARACAO AS PRAZO
					 FROM DBO.TELE_ORC AS A
						  INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
														    ----AND A.CD_FILIAL = D.CD_FILIAL
					WHERE A.CD_FILIAL = @CD_FILIAL
					  AND A.CD_ETAPAS = 2
					  AND A.CD_TIPO = 0
					  AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
					UNION ALL
					SELECT 2 AS CD_ETAPAS
						  ,DATEDIFF(MINUTE, GETDATE(), A.ENT_DTHR) - D.SEPARACAO AS PRAZO
					  FROM DBO.TELE_ORC AS A
						   INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
															   --AND A.CD_FILIAL = D.CD_FILIAL
					 WHERE A.CD_FILIAL = @CD_FILIAL
					   AND A.CD_ETAPAS = 2
					   AND A.CD_TIPO = 3
					   AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
					UNION ALL
					SELECT 2 AS CD_ETAPAS
						  ,DATEDIFF(MINUTE, GETDATE(), A.ENT_DTHR) - D.SEPARACAO AS PRAZO
					  FROM DBO.TELE_ORC AS A
						   INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
															   --AND A.CD_FILIAL = D.CD_FILIAL
					 WHERE A.CD_FILIAL = @CD_FILIAL
					   AND A.CD_ETAPAS = 2
					   AND A.CD_TIPO = 1
					   AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
					UNION ALL
					SELECT  1 AS CD_ETAPAS
						   ,DATEDIFF(MINUTE, GETDATE(), A.ENT_DTHR) - D.SEPARACAO AS PRAZO
					  FROM DBO.TELE_ORC AS A
						   INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
															   --AND A.CD_FILIAL = D.CD_FILIAL
					 WHERE A.CD_FILIAL = @CD_FILIAL
					   AND A.CD_ETAPAS = 2
					   AND A.CD_TIPO = 2
					   AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
					UNION ALL
					SELECT 3 AS CD_ETAPAS
						  ,DATEDIFF(MINUTE, GETDATE(), A.ENT_DTHR) - D.SEPARACAO AS PRAZO
					  FROM DBO.TELE_ORC AS A
						   INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
															   --AND A.CD_FILIAL = D.CD_FILIAL
					 WHERE A.CD_FILIAL = @CD_FILIAL
					   AND A.CD_ETAPAS = 3
					   AND A.CD_TIPO = 2
					   AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
					UNION ALL
					SELECT   4 AS CD_ETAPAS
							,DATEDIFF(MINUTE, GETDATE(), A.ENT_DTHR) - D.SEPARACAO AS PRAZO
					  FROM DBO.TELE_ORC AS A
							INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
																--AND A.CD_FILIAL = D.CD_FILIAL
					 WHERE A.CD_FILIAL = @CD_FILIAL
					   AND A.CD_ETAPAS = 4
					   AND A.CD_TIPO = 0
					   AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
					UNION ALL
					SELECT	 5 AS CD_ETAPAS
							,3 AS PRAZO
				      FROM DBO.TELE_ORC AS A
							INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
																--AND A.CD_FILIAL = D.CD_FILIAL
					 WHERE A.CD_FILIAL = @CD_FILIAL
					   AND A.CD_ETAPAS = 5
					   AND A.CD_TIPO = 0
					   AND A.DTHR_PEDIDO > DATEADD(MINUTE, - 720, GETDATE())
			
					UNION ALL
			
					SELECT 6 AS CD_ETAPAS
				,3 AS PRAZO
			FROM DBO.TELE_ORC AS A
			INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
			--AND A.CD_FILIAL = D.CD_FILIAL
			WHERE A.CD_FILIAL = @CD_FILIAL
				AND A.CD_ETAPAS = 6
				AND A.CD_TIPO = 1
				AND A.DTHR_PEDIDO > DATEADD(MINUTE, - 720, GETDATE())
			
			UNION ALL
			
			SELECT 7 AS CD_ETAPAS
				,3 AS PRAZO
			FROM DBO.TELE_ORC AS A
			INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
			--AND A.CD_FILIAL = D.CD_FILIAL
			WHERE A.CD_FILIAL = @CD_FILIAL
				AND A.CD_ETAPAS = 7
				AND A.CD_TIPO = 1
				AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
			UNION ALL
			
			SELECT 8 AS CD_ETAPAS
				,DATEDIFF(MINUTE, GETDATE(), A.ENT_DTHR) - D.SEPARACAO AS PRAZO
			FROM DBO.TELE_ORC AS A
			INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
			--AND A.CD_FILIAL = D.CD_FILIAL
			WHERE A.CD_FILIAL = @CD_FILIAL
				AND A.CD_ETAPAS = 8
				AND A.CD_TIPO = 0
				AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
			UNION ALL
			
			SELECT 8 AS CD_ETAPAS
				,DATEDIFF(MINUTE, GETDATE(), A.ENT_DTHR) - D.SEPARACAO AS PRAZO
			FROM DBO.TELE_ORC AS A
			INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
			--AND A.CD_FILIAL = D.CD_FILIAL
			WHERE A.CD_FILIAL = @CD_FILIAL
				AND A.CD_ETAPAS = 8
				AND A.CD_TIPO = 1
				AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
			UNION ALL
			
			SELECT 9 AS CD_ETAPAS
				  ,3 AS PRAZO
			FROM DBO.TELE_ORC AS A
				 INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
													 --AND A.CD_FILIAL = D.CD_FILIAL
			WHERE A.CD_FILIAL = @CD_FILIAL
			  AND A.CD_ETAPAS = 9
			  AND A.CD_TIPO = 0
			  AND A.DTHR_PEDIDO > DATEADD(MONTH, - 3, GETDATE())
			
			UNION ALL
			SELECT 10 AS CD_ETAPAS
				  ,3 AS PRAZO
			  FROM DBO.TELE_ORC AS A
					INNER JOIN DBO.V_TELE_TEMPO_ETAPA AS D ON A.CD_TIPO_ENTREGA = D.CD_TIPO_ENTREGA
														--AND A.CD_FILIAL = D.CD_FILIAL
			 WHERE A.CD_FILIAL = @CD_FILIAL
			   AND A.CD_ETAPAS = 10
			   AND A.CD_TIPO = 0
			   AND A.DTHR_PEDIDO > DATEADD(MINUTE, - 720, GETDATE()) ) PEDIDO_ETAPA
		) FIM
	GROUP BY FIM.CD_ETAPAS
		,FIM.TIPO

	RETURN
END
