PRINT '> NFE - SCRIPT #0012';

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_NFE_VALIDACAO_POR_HORA]'))
DROP VIEW [dbo].[V_NFE_VALIDACAO_POR_HORA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[V_NFE_VALIDACAO_POR_HORA]
AS
WITH
	NFE_VALIDADAS AS (
		SELECT 
			--NF.DT_EMI_NF
			NFE.NR_AUTORIZADOR
			, CONVERT(VARCHAR(8), NFE.DT_RECBTO, 112) DATA
			, CONVERT(VARCHAR(2), NFE.DT_RECBTO, 108) HORA
			, COUNT(*) AS SAIDA
		FROM EST_NF_SAI NF
		INNER JOIN EST_NF_SERIE SERIE ON
				NF.CD_NF_SERIE = SERIE.CD_NF_SERIE
			AND SERIE.ESPC = 'NFE'
		INNER JOIN EST_NF_SAI_NFE NFE ON
				NF.CD_EMP = NFE.CD_EMP
			AND NF.CD_FILIAL = NFE.CD_FILIAL
			AND NF.CD_NF = NFE.CD_NF
		WHERE
				NF.DT_EMI_NF >= CONVERT(CHAR(10), GETDATE()-3, 112)
			AND NFE.NR_PROTOCOLO IS NOT NULL
			AND NFE.NR_AUTORIZADOR IS NOT NULL
		GROUP BY NFE.NR_AUTORIZADOR, NFE.DT_RECBTO
	)
	, VALIDADAS_POR_HORA AS (
		SELECT 
			DATA
			, HORA
			, COUNT(*) QTDE
		FROM 
			NFE_VALIDADAS
		GROUP BY DATA, HORA
		--ORDER BY DATA, HORA
	)
	SELECT
		V.DATA
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '00'),0) AS 'H00'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '01'),0) AS 'H01'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '02'),0) AS 'H02'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '03'),0) AS 'H03'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '04'),0) AS 'H04'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '05'),0) AS 'H05'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '06'),0) AS 'H06'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '07'),0) AS 'H07'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '08'),0) AS 'H08'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '09'),0) AS 'H09'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '10'),0) AS 'H10'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '11'),0) AS 'H11'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '12'),0) AS 'H12'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '13'),0) AS 'H13'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '14'),0) AS 'H14'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '15'),0) AS 'H15'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '16'),0) AS 'H16'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '17'),0) AS 'H17'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '18'),0) AS 'H18'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '19'),0) AS 'H19'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '20'),0) AS 'H20'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '21'),0) AS 'H21'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '22'),0) AS 'H22'
		, ISNULL((SELECT QTDE FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA AND T.HORA = '23'),0) AS 'H23'
		, ISNULL((SELECT SUM(QTDE) FROM VALIDADAS_POR_HORA T WHERE T.DATA = V.DATA),0) AS 'DIA'
	FROM
		VALIDADAS_POR_HORA V
	GROUP BY V.DATA
	--ORDER BY V.DATA DESC

GO

PRINT '    < [OK]';
