none
Duvida em query com registro de horários após as 00:00 RRS feed

  • Pergunta

  • Boa tarde,

     Eu tenho a seguinte query:

    SELECT 
    FUNC.FILIAL AS FILIAL,
    FUNC.COLIGADA              AS CODCOLIGADA,
    FUNC.CHAPA			       AS CHAPA,
    FUNC.NOME                  AS NOME,
    BANCO.CODEVENTO            AS BCO_EVENTO,
    FUNC.SECAO				   AS SECAO,
    FUNC.NSECAO				   AS NOME_SECAO,
    CONVERT(DATE,PONTO.DATA)   AS DATA,
    	BATIDA1 = MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 1 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ),
    	BATIDA2 = MAX(  CASE WHEN BATIDAS.SEQUENCIALBATIDA = 2 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ),
    	BATIDA3 = MAX(  CASE WHEN BATIDAS.SEQUENCIALBATIDA = 3 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ),
    	BATIDA4 = MAX(  CASE WHEN BATIDAS.SEQUENCIALBATIDA = 4 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ),
    	BATIDA5 = MAX(  CASE WHEN BATIDAS.SEQUENCIALBATIDA = 5 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ),
    	BATIDA6 = MAX(  CASE WHEN BATIDAS.SEQUENCIALBATIDA = 6 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END )
    	FROM 
         VIEW_DADOS_FUNC AS FUNC
    	  LEFT JOIN AAFHTFUN AS PONTO
    			 ON PONTO.CHAPA       = FUNC.CHAPA
    			AND PONTO.CODCOLIGADA = FUNC.COLIGADA
    		LEFT JOIN view_dados_ponto AS BANCO
    			 ON BANCO.CHAPA       = FUNC.CHAPA
    			AND BANCO.CODCOLIGADA = FUNC.COLIGADA
    			AND PONTO.DATA = BANCO.DATA
    		LEFT JOIN ARELBATIDATRANSITOVIEW AS BATIDAS ON
    				BATIDAS.CHAPA = FUNC.CHAPA
    				AND BATIDAS.CHAPA = FUNC.CHAPA
    				AND BATIDAS.CODCOLIGADA = FUNC.COLIGADA
    				AND PONTO.DATA = BATIDAS.DATA 
    			 WHERE
    				FUNC.COLIGADA = 01
    				AND FUNC.CHAPA = '0001'
    				AND CONVERT(DATE,PONTO.DATA) BETWEEN '2019-05-01' AND '2019-05-31'
    			 	GROUP BY FUNC.FILIAL,FUNC.COLIGADA,FUNC.CHAPA,FUNC.NOME,BANCO.CODEVENTO,FUNC.SECAO,FUNC.NSECAO,PONTO.DATA
    				ORDER BY PONTO.DATA

    Ela funciona bem, o meu problema é quando um funcionário fica após a 00:00, ele quebra pro próximo dia, como eu poderia esse tipo de situação ? alguma dica ?

    Vejam um exemplo de como esta ficando:

    2019-05-01	00:43:00	NULL	NULL
    2019-05-02	19:03:00	NULL	NULL
    2019-05-03	00:09:00	19:00:00	23:49:00
    2019-05-04	19:05:00	NULL	NULL
    2019-05-05	00:02:00	NULL	NULL
    2019-05-06	19:06:00	23:40:00	NULL
    2019-05-07	19:02:00	23:50:00	NULL
    2019-05-08	19:01:00	NULL	NULL
    2019-05-09	00:03:00	19:03:00	23:54:00
    2019-05-10	19:05:00	NULL	NULL
    2019-05-11	00:19:00	19:09:00	NULL
    2019-05-12	00:05:00	NULL	NULL
    2019-05-13	19:02:00	23:45:00	NULL
    2019-05-14	19:02:00	23:35:00	NULL
    2019-05-15	19:04:00	23:38:00	NULL
    2019-05-16	19:05:00	23:49:00	NULL
    2019-05-17	19:04:00	23:38:00	NULL
    2019-05-18	19:04:00	23:52:00	NULL
    2019-05-19	NULL	NULL	NULL
    2019-05-20	19:04:00	23:33:00	NULL
    2019-05-21	19:04:00	23:59:00	NULL
    2019-05-22	19:01:00	23:49:00	NULL
    2019-05-23	19:05:00	23:42:00	NULL
    2019-05-24	19:03:00	23:46:00	NULL
    2019-05-25	19:07:00	23:45:00	NULL
    2019-05-26	NULL	NULL	NULL
    2019-05-27	19:04:00	23:36:00	NULL
    2019-05-28	19:04:00	23:39:00	NULL
    2019-05-29	19:03:00	23:41:00	NULL
    2019-05-30	19:04:00	23:46:00	NULL
    2019-05-31	19:01:00	23:47:00	NULL

    Segue imagem de exemplo de como deveria ficar:


    • Editado otaciojb terça-feira, 11 de junho de 2019 17:45
    terça-feira, 11 de junho de 2019 17:41

Respostas

Todas as Respostas

  • Otaciojb,

    Já tentou utilizas funções em utilizas as funções IsNull ou Coalesce()?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Sugerido como Resposta IgorFKModerator quarta-feira, 12 de junho de 2019 12:08
    terça-feira, 11 de junho de 2019 23:22
  • Mais como poderia aplicar? eu verifiquei as funções que você passou, mais não entendi bem como usa-las no que preciso, você teria algum exemplo ?
    quarta-feira, 12 de junho de 2019 12:09
  • Otaciojb,

    Veja se este exemplo te ajuda, alterando inicialmente Batida1:

    -- IsNull --
    
    BATIDA1 = IsNull(MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 1 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ),0)
    
    
    -- Coalesce --
    
    BATIDA1 = Coalesce(MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 1 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ),0)

    A expressão COALESCE é um atalho sintático para a expressão CASE. Ou seja, o código COALESCE(expression1, ...n) é reescrito pelo otimizador de consulta como a seguinte expressão CASE.

    No caso, você poderia utilizar a Coalesce() repassando nos parâmetros da função possíveis valores que podem ser retornados ou até mesmo colunas em caso da ocorrência de nulos.

    Veja o exemplo:

    No exemplo a seguir, a tabela wages inclui três colunas que contêm informações sobre o salário anual dos funcionários: valor por hora, salário e comissão. No entanto, um funcionário recebe apenas um tipo de pagamento. Para determinar o valor total pago a todos os funcionários, use a função COALESCE para receber apenas o valor não nulo encontrado em hourly_wage, salary e commission.

    CREATE TABLE dbo.wages  
    (  
        emp_id        tinyint   identity,  
        hourly_wage   decimal   NULL,  
        salary        decimal   NULL,  
        commission    decimal   NULL,  
        num_sales     tinyint   NULL  
    );  
    GO  
    INSERT dbo.wages (hourly_wage, salary, commission, num_sales)  
    VALUES  
        (10.00, NULL, NULL, NULL),  
        (20.00, NULL, NULL, NULL),  
        (30.00, NULL, NULL, NULL),  
        (40.00, NULL, NULL, NULL),  
        (NULL, 10000.00, NULL, NULL),  
        (NULL, 20000.00, NULL, NULL),  
        (NULL, 30000.00, NULL, NULL),  
        (NULL, 40000.00, NULL, NULL),  
        (NULL, NULL, 15000, 3),  
        (NULL, NULL, 25000, 2),  
        (NULL, NULL, 20000, 6),  
        (NULL, NULL, 14000, 4);  
    GO  
    SET NOCOUNT OFF;  
    GO  
    SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
       salary,   
       commission * num_sales) AS money) AS 'Total Salary'   
    FROM dbo.wages  
    ORDER BY 'Total Salary';  
    GO  
     

    Resultado:Total Salary 
    ------------ 
    10000.00 
    20000.00 
    20800.00 
    30000.00 
    40000.00 
    41600.00 
    45000.00 
    50000.00 
    56000.00 
    62400.00 
    83200.00 
    120000.00 

    Referência: https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 12 de junho de 2019 12:15
  • Oi Junior, 

     Obrigado pela informação, inclusive muito útil  e inclusive favoritei para estudos.

     Mais pelo que vi eu não me expressei bem na pergunta,  o que na verdade preciso é organizar o resultado, veja o exemplo da imagem abaixo, nele o primeiro exemplo é o que esta incorreto, e o segundo é o que espero.

    O que seria, sempre que ultrapassar a meia noite, ele mostrar no dia anterior.

    quarta-feira, 12 de junho de 2019 12:37
  • Otaciojb,

    Ok, bom, então mas qual deve ser o critério para mostrar por exemplo no dia 2019-05-03 o valor 19:00 e não 00:09:00?

    Talvez você tenha que aplicar alguma condição no seu Case que verifique se mudou a data e a hora e neste caso, considerar da data anterior e o último horário desta respectiva data.

    Como estes dados estão sendo armazenados?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 12 de junho de 2019 13:19
  • São armazenados linha a linha, em minha view que mostro eles por data, segue  esse nosso exemplo armazenado:

    CHAPA  COL  DATA                    BATIDA                  ST  NAT  SEQUENCIALBATIDA
     
    9612	1	2019-05-01 00:00:00.000	2019-05-01 00:43:00.000	C	 1	 1
    9612	1	2019-05-02 00:00:00.000	2019-05-02 19:03:00.000	C	 0	 1
    9612	1	2019-05-03 00:00:00.000	2019-05-03 00:09:00.000	C	 1	 1
    9612	1	2019-05-03 00:00:00.000	2019-05-03 19:00:00.000	C	 0	 2
    9612	1	2019-05-03 00:00:00.000	2019-05-03 23:49:00.000	C	 1	 3
    9612	1	2019-05-04 00:00:00.000	2019-05-04 19:05:00.000	C	 0	 1
    9612	1	2019-05-05 00:00:00.000	2019-05-05 00:02:00.000	C	 1	 1
    9612	1	2019-05-06 00:00:00.000	2019-05-06 19:06:00.000	C	 0	 1
    9612	1	2019-05-06 00:00:00.000	2019-05-06 23:40:00.000	C	 1	 2
    9612	1	2019-05-07 00:00:00.000	2019-05-07 19:02:00.000	C	 0	 1
    9612	1	2019-05-07 00:00:00.000	2019-05-07 23:50:00.000	C	 1	 2


    quarta-feira, 12 de junho de 2019 14:09
  • Boa tarde,

    Não sei se entendi corretamente os dados mas experimente fazer uns testes trocando o trecho abaixo:

    CONVERT(DATE,PONTO.DATA)
    

    por

    CONVERT(DATE, PONTO.DATA - CASE WHEN BATIDAS.NAT = 1 AND BATIDAS.SEQUENCIALBATIDA = 1 THEN 1 ELSE 0 END)

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Sugerido como Resposta DBA.NOTURNO quinta-feira, 13 de junho de 2019 18:34
    quarta-feira, 12 de junho de 2019 17:24
  • Oi @gapimex

    Quase deu certo, veja:

    DATA DATA_CASE

    2019-05-04 2019-05-04 19:05:00 NULL 2019-05-05 2019-05-04 00:02:00 NULL

    Veja a coluna DATA_CASE gerei ela com usa dica, então, quando seu teste é positivo, ele posicionar na data anterior da forma que preciso, mais esta faltando somente mais uma coisa, eu precisaria que essa linha se agrupasse, exemplo:

    2019-05-04	2019-05-04	19:05:00	00:02:00
    

    Eu pensei em tirar a primeira data, a minha e usar o GROUP BY mais não vai dar certo por conta de as horas são diferentes, teria alguma dica de como poderia fazer ?


    quarta-feira, 12 de junho de 2019 17:43
  • Além de deixar somente a data modificada com o Case, experimente alterar o trecho abaixo:

    	BATIDA1 = MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 1 AND BATIDAS.NAT = 0 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ),
    	BATIDA2 = MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 2 OR ( BATIDAS.SEQUENCIALBATIDA = 1 AND BATIDAS.NAT = 1 ) THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ),

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Sugerido como Resposta DBA.NOTURNO quinta-feira, 13 de junho de 2019 18:34
    quarta-feira, 12 de junho de 2019 18:05
  • Otaciojb,

    Se algo invês de você utilizer uma View, você utilizar uma CTE, na qual a CTE seria o mesmo Código da View, sendo os Order By, e no momento da execução você poderia fazer os demais agrupamentos mesmo já tento os dados agrupados.

    O que acha?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Sugerido como Resposta IgorFKModerator quinta-feira, 13 de junho de 2019 13:32
    quarta-feira, 12 de junho de 2019 18:08
  • Obrigado pela dica, vou pesquisar sobre, é que não domino muito o CTE
    quarta-feira, 12 de junho de 2019 20:23
  • otaciojb

    mas como saber que uma batida é de entrada ou de saida?

    quinta-feira, 13 de junho de 2019 18:26
  • DBA.Noturno,

    Aparentemente é com base no horário sendo comparado entre as respectivas batidas, eu também, fiquei com esta dúvida.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 14 de junho de 2019 10:22
  • Ele tem o campo 

    SEQUENCIALBATIDA

    Só que não é fixo, exemplo, se a pessoa bater 4x o ponto naquele dia  o ultimo será 4, se bater 2 será 2, to tentando algo como MAX(SEQUENCIALBATIDA

    Veja um exemplo de um dia com 3 batidas:

    9612	1	2019-05-03 00:00:00.000	2019-05-03 00:09:00.000	C	 1	 1 < SequencialBatida
    9612	1	2019-05-03 00:00:00.000	2019-05-03 19:00:00.000	C	 0	 2 < SequencialBatida
    9612	1	2019-05-03 00:00:00.000	2019-05-03 23:49:00.000	C	 1	 3 < SequencialBatida

    sexta-feira, 14 de junho de 2019 12:27
  • Otaciojb,

    Então, se tomarmos como base o exemplo, basta então você agrupar pela coluna que contem o código e utilizar a função Max() justamente na coluna que contem o sequencial da batida.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    domingo, 16 de junho de 2019 00:10
  • São armazenados linha a linha, em minha view que mostro eles por data, segue  esse nosso exemplo armazenado:

    CHAPA  COL  DATA                    BATIDA                  ST  NAT  SEQUENCIALBATIDA
     
    9612	1	2019-05-01 00:00:00.000	2019-05-01 00:43:00.000	C	 1	 1
    9612	1	2019-05-02 00:00:00.000	2019-05-02 19:03:00.000	C	 0	 1
    9612	1	2019-05-03 00:00:00.000	2019-05-03 00:09:00.000	C	 1	 1
    9612	1	2019-05-03 00:00:00.000	2019-05-03 19:00:00.000	C	 0	 2
    9612	1	2019-05-03 00:00:00.000	2019-05-03 23:49:00.000	C	 1	 3
    9612	1	2019-05-04 00:00:00.000	2019-05-04 19:05:00.000	C	 0	 1
    9612	1	2019-05-05 00:00:00.000	2019-05-05 00:02:00.000	C	 1	 1
    9612	1	2019-05-06 00:00:00.000	2019-05-06 19:06:00.000	C	 0	 1
    9612	1	2019-05-06 00:00:00.000	2019-05-06 23:40:00.000	C	 1	 2
    9612	1	2019-05-07 00:00:00.000	2019-05-07 19:02:00.000	C	 0	 1
    9612	1	2019-05-07 00:00:00.000	2019-05-07 23:50:00.000	C	 1	 2

    Acho que tem alterar a regra de junção.

    DE

    AND PONTO.DATA = BATIDAS.DATA

    PARA

    AND PONTO.DATA = (CASE WHEN BATIDAS.SEQUENCIALBATIDA = 1 AND BATIDAS.NAT = 1 THEN DATEADD(DAY, -1, BATIDAS.DATA) ELSE BATIDAS.DATA END)

      

    Mas vai ter que reescrever esta parte:

    BATIDA1 = MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 1 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ), BATIDA2 = MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 2 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ), BATIDA3 = MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 3 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ), BATIDA4 = MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 4 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ), BATIDA5 = MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 5 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END ), BATIDA6 = MAX( CASE WHEN BATIDAS.SEQUENCIALBATIDA = 6 THEN CAST( BATIDAS.BATIDA AS TIME( 0 )) END )


    • Editado DBA.NOTURNO domingo, 16 de junho de 2019 06:44
    domingo, 16 de junho de 2019 06:21
  • Deleted
    • Marcado como Resposta otaciojb terça-feira, 25 de junho de 2019 20:19
    • Não Marcado como Resposta otaciojb terça-feira, 25 de junho de 2019 20:26
    terça-feira, 18 de junho de 2019 12:35
  • @José Diz , 

     Boa tarde,

     Chegando a sua logica e suas querys fui seguindo o caminho, onde deu 90% certo,

    Veja:

    COLIGADA CHAPA NAT DATA BATIDA SEQUENCIAL_BATIDA MEIA_NOITE

    1 9612 1 2019-04-30 00:00:00.000 2019-04-30 00:43:00.000 2 1 1 9612 0 2019-05-02 00:00:00.000 2019-05-02 19:03:00.000 1 0 1 9612 1 2019-05-02 00:00:00.000 2019-05-02 00:09:00.000 2 1 1 9612 0 2019-05-03 00:00:00.000 2019-05-03 19:00:00.000 2 0 1 9612 1 2019-05-03 00:00:00.000 2019-05-03 23:49:00.000 3 0 1 9612 0 2019-05-04 00:00:00.000 2019-05-04 19:05:00.000 1 0 1 9612 1 2019-05-04 00:00:00.000 2019-05-04 00:02:00.000 2 1 1 9612 0 2019-05-06 00:00:00.000 2019-05-06 19:06:00.000 1 0 1 9612 1 2019-05-06 00:00:00.000 2019-05-06 23:40:00.000 2 0 1 9612 0 2019-05-07 00:00:00.000 2019-05-07 19:02:00.000 1 0 1 9612 1 2019-05-07 00:00:00.000 2019-05-07 23:50:00.000 2 0 1 9612 0 2019-05-08 00:00:00.000 2019-05-08 19:01:00.000 1 0 1 9612 1 2019-05-08 00:00:00.000 2019-05-08 00:03:00.000 2 1 1 9612 0 2019-05-09 00:00:00.000 2019-05-09 19:03:00.000 2 0 1 9612 1 2019-05-09 00:00:00.000 2019-05-09 23:54:00.000 3 0 1 9612 0 2019-05-10 00:00:00.000 2019-05-10 19:05:00.000 1 0 1 9612 1 2019-05-10 00:00:00.000 2019-05-10 00:19:00.000 2 1 1 9612 0 2019-05-11 00:00:00.000 2019-05-11 19:09:00.000 2 0 1 9612 1 2019-05-11 00:00:00.000 2019-05-11 00:05:00.000 2 1 1 9612 0 2019-05-13 00:00:00.000 2019-05-13 19:02:00.000 1 0 1 9612 1 2019-05-13 00:00:00.000 2019-05-13 23:45:00.000 2 0 1 9612 0 2019-05-14 00:00:00.000 2019-05-14 19:02:00.000 1 0 1 9612 1 2019-05-14 00:00:00.000 2019-05-14 23:35:00.000 2 0 1 9612 0 2019-05-15 00:00:00.000 2019-05-15 19:04:00.000 1 0 1 9612 1 2019-05-15 00:00:00.000 2019-05-15 23:38:00.000 2 0 1 9612 0 2019-05-16 00:00:00.000 2019-05-16 19:05:00.000 1 0 1 9612 1 2019-05-16 00:00:00.000 2019-05-16 23:49:00.000 2 0 1 9612 0 2019-05-17 00:00:00.000 2019-05-17 19:04:00.000 1 0 1 9612 1 2019-05-17 00:00:00.000 2019-05-17 23:38:00.000 2 0 1 9612 0 2019-05-18 00:00:00.000 2019-05-18 19:04:00.000 1 0 1 9612 1 2019-05-18 00:00:00.000 2019-05-18 23:52:00.000 2 0 1 9612 0 2019-05-20 00:00:00.000 2019-05-20 19:04:00.000 1 0 1 9612 1 2019-05-20 00:00:00.000 2019-05-20 23:33:00.000 2 0 1 9612 0 2019-05-21 00:00:00.000 2019-05-21 19:04:00.000 1 0 1 9612 1 2019-05-21 00:00:00.000 2019-05-21 23:59:00.000 2 0 1 9612 0 2019-05-22 00:00:00.000 2019-05-22 19:01:00.000 1 0 1 9612 1 2019-05-22 00:00:00.000 2019-05-22 23:49:00.000 2 0 1 9612 0 2019-05-23 00:00:00.000 2019-05-23 19:05:00.000 1 0 1 9612 1 2019-05-23 00:00:00.000 2019-05-23 23:42:00.000 2 0 1 9612 0 2019-05-24 00:00:00.000 2019-05-24 19:03:00.000 1 0 1 9612 1 2019-05-24 00:00:00.000 2019-05-24 23:46:00.000 2 0 1 9612 0 2019-05-25 00:00:00.000 2019-05-25 19:07:00.000 1 0 1 9612 1 2019-05-25 00:00:00.000 2019-05-25 23:45:00.000 2 0 1 9612 0 2019-05-27 00:00:00.000 2019-05-27 19:04:00.000 1 0 1 9612 1 2019-05-27 00:00:00.000 2019-05-27 23:36:00.000 2 0 1 9612 0 2019-05-28 00:00:00.000 2019-05-28 19:04:00.000 1 0 1 9612 1 2019-05-28 00:00:00.000 2019-05-28 23:39:00.000 2 0 1 9612 0 2019-05-29 00:00:00.000 2019-05-29 19:03:00.000 1 0 1 9612 1 2019-05-29 00:00:00.000 2019-05-29 23:41:00.000 2 0 1 9612 0 2019-05-30 00:00:00.000 2019-05-30 19:04:00.000 1 0 1 9612 1 2019-05-30 00:00:00.000 2019-05-30 23:46:00.000 2 0 1 9612 0 2019-05-31 00:00:00.000 2019-05-31 19:01:00.000 1 0 1 9612 1 2019-05-31 00:00:00.000 2019-05-31 23:47:00.000 2 0


    Meu unico problema é:

    Veja essa linha:

    1	9612	0	2019-05-03 00:00:00.000	2019-05-03 19:00:00.000	2	0
    1	9612	1	2019-05-03 00:00:00.000	2019-05-03 23:49:00.000	3	0

    Como manipulei o horario da meia noite meu sequencial batida esta seguindo a ordem do banco no caso 2 e 3,

    Eu consigo nessa query abaixo que esta boa pra mim, ajustar o sequencial batida ?

     Que seria o seguinte, um sequencial começando do 1 por dia, ou seja, se em um dia tivesse 5 , seria 12345 e no próximo dia começaria demovo e assim por diante,

    Query atual:

    SELECT 
       BATIDAS.CODCOLIGADA AS CODCOLIGADA
     , BATIDAS.CHAPA       AS CHAPA
     , BATIDAS.NATUREZA   AS NATUREZA
    
     , ( CASE  
    		WHEN  MEIA_NOITE.BATIDA IS NOT NULL 
    		THEN  BATIDAS.DATA - 1
    		ELSE  BATIDAS.DATA 
    	 END ) AS DATA 
    
    
      , ( CASE  
    		WHEN MEIA_NOITE.BATIDA IS NOT NULL 
    		THEN MEIA_NOITE.BATIDA - 1
    		ELSE BATIDAS.BATIDA  
    	 END ) AS BATIDA 
    
       , ( CASE  
    		WHEN  MEIA_NOITE.BATIDA IS NOT NULL 
    		THEN  BATIDAS.SEQUENCIALBATIDA +1
    		ELSE  BATIDAS.SEQUENCIALBATIDA
    	 END ) AS SEQUENCIALBATIDA
    
      , ( CASE  
    		WHEN MEIA_NOITE.BATIDA IS NOT NULL 
    		THEN 1
    		ELSE 0  
    	 END ) AS MEIA_NOITE 
    
     FROM 
    
        ARELBATIDATRANSITOVIEW AS BATIDAS
    	LEFT JOIN VIEW_BATIDAS_MEIA_NOITE AS MEIA_NOITE
    		ON  MEIA_NOITE.CHAPA            = BATIDAS.CHAPA                           
    		AND MEIA_NOITE.CODCOLIGADA      = BATIDAS.CODCOLIGADA                                    
    		AND MEIA_NOITE.DATA             = BATIDAS.DATA                             
    		AND MEIA_NOITE.BATIDA           = BATIDAS.BATIDA 
    
    	 WHERE
    		BATIDAS.CHAPA = 9612
    	   AND BATIDAS.BATIDA BETWEEN '2019-05-01 00:00:00.000' AND '2019-05-31 23:59:59.000';

    terça-feira, 25 de junho de 2019 19:57
  • Deleted
    terça-feira, 25 de junho de 2019 20:34
  • @Jose Diz,

     Boa noite,

    Ao executar seu codigo em uma base de teste para estudos e entende-lo ele retornou essa mensagem:

    Mensagem 4406, Nível 16, Estado 1, Linha 14
    Falha ao atualizar ou inserir a exibição ou a função 'T1' porque ele contém um campo derivado ou constante.

    Mais a questão nem foi no erro, mais é que essa tabela não posso fazer updates nela.

    Teria alguma outra forma de tratar somente com querys ?

    quarta-feira, 26 de junho de 2019 01:01
  • Deleted
    • Marcado como Resposta otaciojb sábado, 3 de agosto de 2019 00:02
    quarta-feira, 26 de junho de 2019 10:20
  • @Jose Diz,

     Agora esta dando essa mensagem:

    Mensagem 242, Nível 16, Estado 3, Linha 18
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
    The statement has been terminated.
    
    (0 linhas afetadas)
    
    (0 linhas afetadas)
    Warning: Null value is eliminated by an aggregate or other SET operation.

    E ai testando eu vi que a tabela temporária #Batidas não esta salvando nada nela, inclusive essa linha 18 é o inicio do select dela, ai com isso como pode ver na tela, ele completa a consulta,mais as batidas ficam vazias:

     

    quarta-feira, 26 de junho de 2019 12:17
  • Deleted
    quarta-feira, 26 de junho de 2019 14:54
  • @José Diz, na verdade quando fui validar pra você, vi que não é uma tabela, e sim uma view nativa do sistema, segue a estrutura dela:

    USE [BASE_TESTE]
    GO
    
    /****** Object:  View [dbo].[ARELBATIDATRANSITOVIEW]    Script Date: 14/06/2019 11:01:07 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    /* Mateus Queiroz Correia - CR1060_02 - Oc - 12/08/2008 */
    CREATE VIEW [dbo].[ARELBATIDATRANSITOVIEW]
    AS
    SELECT A.CHAPA
    	,A.CODCOLIGADA
    	,CONVERT(DATETIME, CONVERT(VARCHAR, MESES.MES) + '/' + CONVERT(VARCHAR, DIAS.DIA) + '/' + CONVERT(VARCHAR, A.ANO)) AS DATA
    	,NULL AS BATIDA
    	,'P' AS STATUS
    	,0 AS NATUREZA
    	,1 AS SEQUENCIALBATIDA
    FROM (
    	SELECT 1 AS DIA
    	UNION
    	SELECT 2 AS DIA
    	UNION
    	SELECT 3 AS DIA
    	UNION
    	SELECT 4 AS DIA
    	UNION
    	SELECT 5 AS DIA
    	UNION
    	SELECT 6 AS DIA
    	UNION
    	SELECT 7 AS DIA
    	UNION
    	SELECT 8 AS DIA
    	UNION
    	SELECT 9 AS DIA
    	UNION
    	SELECT 10 AS DIA
    	UNION
    	SELECT 11 AS DIA
    	UNION
    	SELECT 12 AS DIA
    	UNION
    	SELECT 13 AS DIA
    	UNION
    	SELECT 14 AS DIA
    	UNION
    	SELECT 15 AS DIA
    	UNION
    	SELECT 16 AS DIA
    	UNION
    	SELECT 17 AS DIA
    	UNION
    	SELECT 18 AS DIA
    	UNION
    	SELECT 19 AS DIA
    	UNION
    	SELECT 20 AS DIA
    	UNION
    	SELECT 21 AS DIA
    	UNION
    	SELECT 22 AS DIA
    	UNION
    	SELECT 23 AS DIA
    	UNION
    	SELECT 24 AS DIA
    	UNION
    	SELECT 25 AS DIA
    	UNION
    	SELECT 26 AS DIA
    	UNION
    	SELECT 27 AS DIA
    	UNION
    	SELECT 28 AS DIA
    	UNION
    	SELECT 29 AS DIA
    	UNION
    	SELECT 30 AS DIA
    	UNION
    	SELECT 31 AS DIA
    	) AS DIAS
    	,(
    		SELECT 1 AS MES
    		UNION
    		SELECT 2 AS MES
    		UNION
    		SELECT 3 AS MES
    		UNION
    		SELECT 4 AS MES
    		UNION
    		SELECT 5 AS MES
    		UNION
    		SELECT 6 AS MES
    		UNION
    		SELECT 7 AS MES
    		UNION
    		SELECT 8 AS MES
    		UNION
    		SELECT 9 AS MES
    		UNION
    		SELECT 10 AS MES
    		UNION
    		SELECT 11 AS MES
    		UNION
    		SELECT 12 AS MES
    		) AS MESES
    LEFT OUTER JOIN (
    	SELECT DISTINCT YEAR(ABATFUN.DATA) AS ANO
    		,ABATFUN.CHAPA
    		,ABATFUN.CODCOLIGADA
    	FROM ABATFUN  WITH (NOLOCK)
    	) AS A ON A.ANO IS NOT NULL
    WHERE MESES.MES IS NOT NULL
    	AND ISDATE(CONVERT(VARCHAR, MESES.MES) + '/' + CONVERT(VARCHAR, DIAS.DIA) + '/' + CONVERT(VARCHAR, A.ANO)) = 1
    	AND NOT EXISTS (
    		SELECT TOP 1 1
    		FROM ABATFUN  WITH (NOLOCK)
    		WHERE ABATFUN.CHAPA = A.CHAPA
    			AND ABATFUN.CODCOLIGADA = A.CODCOLIGADA
    			AND DAY(ABATFUN.DATA) = DIAS.DIA
    			AND MONTH(ABATFUN.DATA) = MESES.MES
    			AND YEAR(ABATFUN.DATA) = A.ANO
    		)
    UNION ALL
    SELECT ABATFUN.CHAPA
    	,ABATFUN.CODCOLIGADA
    	,ABATFUN.DATA
    	,DATEADD(MINUTE, ABATFUN.BATIDA, ABATFUN.DATA) AS BATIDA
    	,ABATFUN.STATUS
    	,ABATFUN.NATUREZA
    	,(
    		SELECT COUNT(A.BATIDA)
    		FROM ABATFUN A
    		WHERE A.CHAPA = ABATFUN.CHAPA
    			AND A.CODCOLIGADA = ABATFUN.CODCOLIGADA
    			AND A.DATA = ABATFUN.DATA
    			AND A.BATIDA <= ABATFUN.BATIDA
    		) AS SEQUENCIALBATIDA
    FROM ABATFUN  WITH (NOLOCK)
    GO

     
    quarta-feira, 26 de junho de 2019 16:29
  • Deleted
    quinta-feira, 27 de junho de 2019 16:45
  • @Jose Diz,

     Segue Retornos:

    USE [BASE_TESTE]
    GO
    
    /****** Object:  Table [dbo].[ABATFUN]    Script Date: 27/06/2019 15:07:40 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[ABATFUN](
    	[CODCOLIGADA] [dbo].[DCODCOLIGADA] NOT NULL,
    	[CHAPA] [dbo].[DCHAPA] NOT NULL,
    	[DATA] [datetime] NOT NULL,
    	[BATIDA] [int] NOT NULL,
    	[STATUS] [varchar](1) NULL,
    	[NATUREZA] [smallint] NULL,
    	[CODIGOTERMCOL] [varchar](10) NULL,
    	[DATAINSERCAO] [datetime] NULL,
    	[DATAAPROV] [datetime] NULL,
    	[COLIGADARESP] [dbo].[DCODCOLIGADANULL] NULL,
    	[CHAPARESP] [dbo].[DCHAPANULL] NULL,
    	[DATAREFERENCIA] [datetime] NULL,
    	[IDJORNADA] [int] NULL,
    	[IDAAFDT] [int] NULL,
    	[RECCREATEDBY] [varchar](50) NULL,
    	[RECCREATEDON] [datetime] NULL,
    	[RECMODIFIEDBY] [varchar](50) NULL,
    	[RECMODIFIEDON] [datetime] NULL,
    	[TIPOBATIDA] [int] NULL,
    	[NATUREZAALTERADA] [smallint] NULL,
    	[IDAPORTARIA373] [int] NULL,
     CONSTRAINT [PKABATFUN] PRIMARY KEY CLUSTERED 
    (
    	[CHAPA] ASC,
    	[CODCOLIGADA] ASC,
    	[DATA] ASC,
    	[BATIDA] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[ABATFUN]  WITH CHECK ADD  CONSTRAINT [FKABATFUN_AAFDT] FOREIGN KEY([CODCOLIGADA], [IDAAFDT])
    REFERENCES [dbo].[AAFDT] ([CODCOLIGADA], [ID])
    GO
    
    ALTER TABLE [dbo].[ABATFUN] CHECK CONSTRAINT [FKABATFUN_AAFDT]
    GO
    
    ALTER TABLE [dbo].[ABATFUN]  WITH CHECK ADD  CONSTRAINT [FKABATFUN_AJORNADACALCULADA] FOREIGN KEY([CODCOLIGADA], [CHAPA], [DATAREFERENCIA], [IDJORNADA])
    REFERENCES [dbo].[AJORNADACALCULADA] ([CODCOLIGADA], [CHAPA], [DATAREFERENCIA], [IDJORNADA])
    GO
    
    ALTER TABLE [dbo].[ABATFUN] CHECK CONSTRAINT [FKABATFUN_AJORNADACALCULADA]
    GO
    
    ALTER TABLE [dbo].[ABATFUN]  WITH CHECK ADD  CONSTRAINT [FKABATFUN_APARFUN] FOREIGN KEY([CODCOLIGADA], [CHAPA])
    REFERENCES [dbo].[APARFUN] ([CODCOLIGADA], [CHAPA])
    GO
    
    ALTER TABLE [dbo].[ABATFUN] CHECK CONSTRAINT [FKABATFUN_APARFUN]
    GO
    
    ALTER TABLE [dbo].[ABATFUN]  WITH CHECK ADD  CONSTRAINT [FKABATFUN_APORTARIA373] FOREIGN KEY([CODCOLIGADA], [IDAPORTARIA373])
    REFERENCES [dbo].[APORTARIA373] ([CODCOLIGADA], [ID])
    GO
    
    ALTER TABLE [dbo].[ABATFUN] CHECK CONSTRAINT [FKABATFUN_APORTARIA373]
    GO
    
    ALTER TABLE [dbo].[ABATFUN]  WITH CHECK ADD  CONSTRAINT [FKABATFUN_ARELCOL] FOREIGN KEY([CODCOLIGADA], [CODIGOTERMCOL])
    REFERENCES [dbo].[ARELCOL] ([CODCOLIGADA], [CODIGO])
    GO
    
    ALTER TABLE [dbo].[ABATFUN] CHECK CONSTRAINT [FKABATFUN_ARELCOL]
    GO
    
    ALTER TABLE [dbo].[ABATFUN]  WITH CHECK ADD  CONSTRAINT [FKABATFUN_PFUNC] FOREIGN KEY([COLIGADARESP], [CHAPARESP])
    REFERENCES [dbo].[PFUNC] ([CODCOLIGADA], [CHAPA])
    GO
    
    ALTER TABLE [dbo].[ABATFUN] CHECK CONSTRAINT [FKABATFUN_PFUNC]
    GO
    
    
    

    quinta-feira, 27 de junho de 2019 18:08
  • Deleted
    sexta-feira, 28 de junho de 2019 11:07
  • Boa tarde José Diz,

     Ao escutar seu código, deu o seguinte erro nos blocos abaixo:

    Erro:

    Mensagem 468, Nível 16, Estado 9, Linha 57
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "Latin1_General_CI_AS" in the equal to operation.

    Blocos:

    UPDATE T1
      set SEQUENCIALBATIDA= T1.SEQUENCIALBATIDA -1
      --output deleted.CODCOLIGADA, deleted.CHAPA, deleted.DATA, inserted.SEQUENCIALBATIDA
      from #Batidas as T1
           inner join @Mod as T2 on T1.CODCOLIGADA = T2.CODCOLIGADA
                                    and T1.CHAPA  = T2.CHAPA
                                    and T1.DATA = T2.DATA;
    
    
    	FROM 
         VIEW_DADOS_FUNC AS FUNC
    	  LEFT JOIN AAFHTFUN AS PONTO
    			 ON PONTO.CHAPA       = FUNC.CHAPA
    			AND PONTO.CODCOLIGADA = FUNC.COLIGADA
    		LEFT JOIN view_dados_ponto AS BANCO
    			 ON BANCO.CHAPA       = FUNC.CHAPA
    			AND BANCO.CODCOLIGADA = FUNC.COLIGADA
    			AND PONTO.DATA = BANCO.DATA
    		LEFT JOIN #Batidas AS BATIDAS ON 
    				BATIDAS.CHAPA = FUNC.CHAPA 
    				AND BATIDAS.CODCOLIGADA = FUNC.COLIGADA
    				AND PONTO.DATA = BATIDAS.DATA 

    Coloquei o COLLATE SQL_Latin1_General_CP1_CI_AI :

    UPDATE T1
      set SEQUENCIALBATIDA= T1.SEQUENCIALBATIDA -1
      --output deleted.CODCOLIGADA, deleted.CHAPA, deleted.DATA, inserted.SEQUENCIALBATIDA
      from #Batidas as T1
           inner join @Mod as T2 on T1.CODCOLIGADA = T2.CODCOLIGADA
                                    and T1.CHAPA  = T2.CHAPA COLLATE SQL_Latin1_General_CP1_CI_AI
                                    and T1.DATA = T2.DATA;

    Executou, mais só que da outros alertas e não exibe as batidas.

    Erro após executar:

    Mensagem 242, Nível 16, Estado 3, Linha 30
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
    The statement has been terminated.
    
    (0 linhas afetadas)
    
    (0 linhas afetadas)
    Warning: Null value is eliminated by an aggregate or other SET operation.
    
    (31 linhas afetadas)


    • Editado otaciojb sexta-feira, 28 de junho de 2019 17:20 Inclusão de imagem
    sexta-feira, 28 de junho de 2019 17:19
  • Deleted
    sexta-feira, 28 de junho de 2019 17:51
  • # Ainda deu erro, veja:


    • Editado otaciojb quarta-feira, 17 de julho de 2019 14:38
    sexta-feira, 28 de junho de 2019 19:20
  • Deleted
    sexta-feira, 19 de julho de 2019 12:55