Usuário com melhor resposta
EXEC (@SQL) Vs SELECT - Coluna Dinâmica

Pergunta
-
Caros,
Existe alguma diferença entre essas 02 consultas?
A Coluna TEMPO na consulta abaixo retorna 17:58:0
SELECT EMPRESA, CONVERT(varchar,DTENTRADA,103) AS DATA, CASE DTENTRADA WHEN NULL THEN '0' ELSE dbo.fn_ConvertTime(SUM(dbo.fn_DiferencaTempo('07:30:00',CONVERT(varchar,DTENTRADA,108),'E'))) END AS TEMPO FROM CATRACAS_RISCO_CANT WHERE CONVERT(TIME,DTENTRADA) > '07:31:00' and CONVERT(TIME,DTENTRADA) < '09:01:00' AND EMPRESA = 'EMPRESA JOTA' and CONVERT(varchar,DTENTRADA,103) = '20/04/2013' GROUP BY EMPRESA, CONVERT(varchar,DTENTRADA,103)
Esta consulta está dentro de uma Function e está retornando '00:00:00' para a COLUNA TEMPO
DECLARE @PERIODO nvarchar(10), @HORACORTE nvarchar(10), @HORAINICIO nvarchar(10), @HORAFINAL nvarchar(10), @EVENTO nvarchar(1), @COLUNA varchar(100), @SQL nvarchar(1000), @EMPRESA nvarchar(100), @TABELA nvarchar(100) -- PARÂMETROS PARA PESQUISA SET @PERIODO = '20/04/2013' SET @HORACORTE = '07:31:00' SET @EVENTO = 'E' SET @HORAINICIO = '06:30:00' SET @HORAFINAL = '09:01:00' SET @TABELA = 'CATRACAS_RISCO_CANT' SET @EMPRESA = 'EMPRESA JOTA' -- DEFININDO QUAL COLUNA BUSCAR AS INFORMAÇÕES IF @EVENTO = 'E' BEGIN SET @COLUNA = 'DTENTRADA' END ELSE BEGIN SET @COLUNA = 'DTSAIDA' END -- ./ FIM DA DEFINIÇÃO DE COLUNA SET @SQL = 'SELECT EMPRESA, CONVERT(varchar,'+@COLUNA+',103) AS DATA, CASE DTENTRADA WHEN NULL THEN ''0'' ELSE dbo.fn_ConvertTime(SUM(dbo.fn_DiferencaTempo('''+@HORACORTE+''',CONVERT(varchar,'+@COLUNA+',108),'''+@EVENTO+'''))) END AS TEMPO FROM '+@TABELA+' WHERE CONVERT(TIME,'+@COLUNA+') > '''+@HORAINICIO+''' and CONVERT(TIME,'+@COLUNA+') < '''+@HORAFINAL+''' AND EMPRESA = '''+@EMPRESA+''' and CONVERT(varchar,'+@COLUNA+',103) = '''+@PERIODO+''' GROUP BY EMPRESA, CONVERT(varchar,'+@COLUNA+',103)' EXEC(@SQL)
Funções auxiliares
1 - Transformar INT em TEMPO - hh:mm:ss
USE [NOVO] GO /****** Object: UserDefinedFunction [dbo].[fn_ConvertTime] Script Date: 05/02/2013 15:46:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Batch submitted through debugger: SQLQuery25.sql|0|0|C:\Users\dell\AppData\Local\Temp\~vs9909.sql CREATE FUNCTION [dbo].[fn_ConvertTime](@SEGUNDOS int) RETURNS nvarchar(10) BEGIN DECLARE @RETORNO nvarchar(100) DECLARE @TEST int = @SEGUNDOS IF @TEST < 0 BEGIN SET @RETORNO = CAST('00:00:00' AS VARCHAR) END ELSE BEGIN SET @RETORNO = (CAST(@SEGUNDOS / 3600 AS VARCHAR) + ':' + CAST(@SEGUNDOS % 3600 / 60 AS VARCHAR) + ':' + CAST(@SEGUNDOS % 60 AS VARCHAR)) END RETURN @RETORNO END GO
2 - Diferença entre Horários
USE [NOVO] GO /****** Object: UserDefinedFunction [dbo].[fn_DiferencaTempo] Script Date: 05/02/2013 15:47:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Batch submitted through debugger: SQLQuery25.sql|0|0|C:\Users\dell\AppData\Local\Temp\~vs9909.sql CREATE FUNCTION [dbo].[fn_DiferencaTempo](@HORACORTE nvarchar(8), @HORAEVENTO nvarchar(8), @EVENTO nvarchar(1)) RETURNS int BEGIN DECLARE @RETORNO int IF @EVENTO = 'E' BEGIN SET @RETORNO = DATEDIFF(SECOND,@HORACORTE,@HORAEVENTO) END ELSE BEGIN SET @RETORNO = DATEDIFF(SECOND,@HORAEVENTO,@HORACORTE) END RETURN @RETORNO END GO
Atualmente utilizo um relatório que possui 2 colunas com informações de ENTRADA e SAÍDA - (dd/mm/aaaa hh:mm:ss)
USUARIO
CARGO
EMPRESA
DTENTRADA - com Data e horário
DTSAIDA - com Data e horário
Por isso utilizo uma função fn_DiferencaTempo()Entrada / 09:30:00 (-) 07:30:00 = HORA ENTRADA - HORA CORTE (Quem entrou atrasado)
Saída / 17:30 (-) 16:30 = HORA CORTE - HORA SAÍDA (Quem saiu antes do horário)- Editado Allen Castro quinta-feira, 2 de maio de 2013 19:08
Respostas
-
Deleted
- Marcado como Resposta Allen Castro sexta-feira, 3 de maio de 2013 13:11
Todas as Respostas
-
-
Deleted
- Marcado como Resposta Allen Castro sexta-feira, 3 de maio de 2013 13:11
-
José, muito obrigado pela atenção.
Realizei a correção para tornar idêntica as consultas e o erro permanecia.
Entretanto além da melhora na função fn_ConvertTime eu deveria incluir outras condições na fn_DiferencaTempo()
Antes
FUNCTION [dbo].[fn_DiferencaTempo](@HORACORTE nvarchar(8), @HORAEVENTO nvarchar(8), @EVENTO nvarchar(1)) RETURNS int BEGIN DECLARE @RETORNO int IF @EVENTO = 'E' BEGIN SET @RETORNO = DATEDIFF(SECOND,@HORACORTE,@HORAEVENTO) END ELSE BEGIN SET @RETORNO = DATEDIFF(SECOND,@HORAEVENTO,@HORACORTE) END RETURN @RETORNO END
Depois
ALTER FUNCTION [dbo].[fn_DiferencaTempo](@HORACORTE nvarchar(8), @HORAEVENTO nvarchar(8), @EVENTO nvarchar(1)) RETURNS int BEGIN DECLARE @RETORNO int IF @HORAEVENTO < @HORACORTE BEGIN IF @EVENTO = 'S' BEGIN SET @RETORNO = DATEDIFF(SECOND,@HORAEVENTO,@HORACORTE) END ELSE BEGIN SET @RETORNO = 0 END END ELSE BEGIN IF @EVENTO = 'E' BEGIN SET @RETORNO = DATEDIFF(SECOND,@HORACORTE,@HORAEVENTO) END ELSE BEGIN SET @RETORNO = 0 END END RETURN @RETORNO END
AFC