none
Converter colunas em linhas com ajustes de ordenação RRS feed

  • Pergunta

  • Pessoal,

     Tenho a seguinte consulta:

    DECLARE 
    @coligada AS INT,
    @chapa    AS INT,
    @dataIni  AS DATE,
    @dataFin  AS DATE;
    
    SET @coligada = 1;
    SET @chapa    = 3596;
    SET @dataIni  = '2019-04-05';
    SET @dataFin  = '2019-04-06' ;
    
    SELECT * FROM VIEW_BATIDAS_PONTO
    	  WHERE
    		 CODCOLIGADA = @coligada 
    	     AND CHAPA   = @chapa
    	     AND CAST(DATA AS date) BETWEEN @dataIni AND @dataFin


    Ela retorna da seguinte forma:

    COLIGADA CHAPA	DATA	BATIDA
    1	 3596	05/04/2019	13:45
    1	 3596	05/04/2019	15:57
    1	 3596	05/04/2019	17:44
    1	 3596	05/04/2019	00:09
    1	 3596	06/04/2019	12:54
    1	 3596	06/04/2019	16:21
    1	 3596	06/04/2019	18:02
    1	 3596	06/04/2019	11:10
    


    Preciso converter esse resultado em linhas, exemplo:

    DATA	       BATIDA1	BATIDA2	 BATIDA3  BATIDA4 BATIDA5 BATIDA6
    05/04/2019	13:45	15:57	 17:44	  00:09		
    06/04/2019	11:10	12:54	 16:21	  18:02		
    

    * Coloquei até 6 batidas, porque podem existir até 6.

    Como faço essa conversão ajustando já a hora, digo ajustando porque como podem ver na consulta o dia 06 as 11:10 esta no ultimo registro, tentei usar o order by mais ai o registro 00:09 vai para o primeiro no dia 05, mas essa não é minha intenção.

    vejam na imagem abaixo o resultado esperado:


    quinta-feira, 18 de julho de 2019 10:25

Respostas

Todas as Respostas

  • Otaciojb,

    Vou tentar ajudar, de uma forma genêrica, estou fora de casa neste momento, sem muitos recursos:

    1 - Inicialmente no exemplo de resultado que você deseja, a primeira coisa a fazer e mudar o seu Select, removendo as coluna CodColigada e Chapa, deixando por enquanto somente Data e Batida.

    Algo similar a este exemplo:

    DECLARE 
    @coligada AS INT,
    @chapa    AS INT,
    @dataIni  AS DATE,
    @dataFin  AS DATE;
    
    SET @coligada = 1;
    SET @chapa    = 3596;
    SET @dataIni  = '2019-04-05';
    SET @dataFin  = '2019-04-06' ;
    
    SELECT Data, Chapa 
    FROM VIEW_BATIDAS_PONTO
    WHERE CODCOLIGADA = @coligada 
    AND CHAPA   = @chapa
    AND CAST(DATA AS date) BETWEEN @dataIni AND @dataFin
    Group By Data, Chapa

    Observe que acrescentei o comando Group By para aplicarmos o agrupamento por data e inicialmente Batida, sem levar em consideração o seu resultado.

    2 - O próximo passo será aplicar o conceito de Pivot, criando em tempo de execução as colunas Batida1, Batida2 .....

    Vou postar inicialmente um exemplo de uso do Pivot, mas somente como orientação:

    A Tabela abaixo mostra o resultado que eu obtive durante os inserts dos dados: 

    Número da Análise Tipo da Análise Hora Início Hora Fim Segundos
    1 None 10:45:59.1966667 10:46:51.0566667 52
    2 Torn_Page_Detection 10:46:51.0566667 10:47:31.9633333 40
    3 CheckSum 10:47:31.9800000 10:48:09.3566667 38
    4 None 10:47:31.9800000 10:48:09.3566667 38
    5 Torn_Page_Detection 10:48:09.3700000 10:48:38.6800000 29
    6 CheckSum 10:48:38.6966667 10:49:13.6800000 35
    7 None 10:48:38.6966667 10:49:13.6933333 35
    8 Torn_Page_Detection 10:49:13.7133333 10:49:47.5100000 34
    9 CheckSum 10:49:47.5100000 10:50:17.3433333 30
    10 None 10:49:47.5100000 10:50:17.3600000 30
    11 Torn_Page_Detection 10:50:17.3766667 10:50:49.7066667 32
    12 CheckSum 10:50:49.7233333 10:51:23.3566667 34
    13 None 10:50:49.7233333 10:51:23.3733333 34
    14 Torn_Page_Detection 10:51:23.3733333 10:51:54.4200000 31
    15 CheckSum 10:51:54.4200000 10:52:29.0466667 35
    16 None 10:51:54.4200000 10:52:29.0500000 35
    17 Torn_Page_Detection 10:52:29.0666667 10:53:00.8300000 31
    18 CheckSum 10:53:00.8466667 10:53:32.1433333 32
    19 None 10:53:00.8466667 10:53:32.1600000 32
    20 Torn_Page_Detection 10:53:32.1766667 10:54:06.6466667 34
    21 CheckSum 10:54:06.6633333 10:54:36.9400000 30
    22 None 10:54:06.6633333 10:54:36.9400000 30
    23 Torn_Page_Detection 10:54:36.9566667 10:55:09.7533333 33
    24 CheckSum 10:55:09.7700000 10:55:41.8866667 32
    25 None 10:55:09.7700000 10:55:41.8866667 32
    26 Torn_Page_Detection 10:55:41.9033333 10:56:15.0166667 34
    27 CheckSum 10:56:15.0166667 10:56:45.6966667 30
    28 None 10:56:15.0166667 10:56:45.7133333 30
    29 Torn_Page_Detection 10:56:45.7133333 10:57:21.7900000 36
    30 CheckSum 10:57:21.7933333 10:57:54.8433333

    33

    Este é o resultado que eu desejo:

    Sumário por segundos None Torn_Page_Detection CheckSum
    Média de processamento….. 34 33 32
    Menor tempo de processamento….. 30 29 30
    Maior tempo de processamento….. 52 40 38
    Somatória do tempo de processamento….. 348 334 329

    Aqui eu apliquei o Pivot:

    — Pivot —
    Select ‘Média de processamento…..’ As ‘Sumário por segundos’, [None],[Torn_Page_Detection],[CheckSum]
    From (Select TipoDaAnaliseRealizada,
    HoraDiferenca
    From PageVerifyTempoDecorrido
    ) As A
    Pivot (Avg(HoraDiferenca) For TipoDaAnaliseRealizada In ([None],[Torn_Page_Detection],[CheckSum])) As Pvt
    
    Union All
    
    Select ‘Menor tempo de processamento…..’, [None],[Torn_Page_Detection],[CheckSum]
    From (Select TipoDaAnaliseRealizada,
    HoraDiferenca
    From PageVerifyTempoDecorrido
    ) As A
    Pivot (Min(HoraDiferenca) For TipoDaAnaliseRealizada In ([None],[Torn_Page_Detection],[CheckSum])) As Pvt
    
    Union All
    
    Select ‘Maior tempo de processamento…..’, [None],[Torn_Page_Detection],[CheckSum]
    From (Select TipoDaAnaliseRealizada,
    HoraDiferenca
    From PageVerifyTempoDecorrido
    ) As A
    Pivot (Max(HoraDiferenca) For TipoDaAnaliseRealizada In ([None],[Torn_Page_Detection],[CheckSum])) As Pvt
    
    Union All
    
    Select ‘Somatória do tempo de processamento…..’, [None],[Torn_Page_Detection],[CheckSum]
    From (Select TipoDaAnaliseRealizada,
    HoraDiferenca
    From PageVerifyTempoDecorrido
    ) As A
    Pivot (Sum(HoraDiferenca) For TipoDaAnaliseRealizada In ([None],[Torn_Page_Detection],[CheckSum])) As Pvt
    Go
    Repito este é somente um exemplo, não estou respeitando ou aplicando suas regras de negócio, espero que de alguma forma te ajude, caso você queira saber mais sobre este exemplo, acesse este link do meu blog: #22 – Para que serve 

    Deixo uma pergunta, para ajudar nos próximos posts: Quantas batidades podem ocorrer por dia?


    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]


    quinta-feira, 18 de julho de 2019 14:28
  • Deleted
    sexta-feira, 19 de julho de 2019 13:23
  • Deleted
    • Marcado como Resposta otaciojb sexta-feira, 19 de julho de 2019 17:12
    sexta-feira, 19 de julho de 2019 13:43
  • Bom dia José Diz,

     Deu certo, mais tive que mudar somente a data da pesquisa, de:

    set @dataIni= convert(date, 5/4/2019, 103);  -- dd/mm/aaaa
    set @dataFin= convert(date, 6/4/2019, 103);
    

    Para:

    SET @dataIni  = '2019-04-05';
    SET @dataFin  = '2019-04-06' ;

    Mais deu tudo certo.

    Obrigado pela ajuda.

    sexta-feira, 19 de julho de 2019 15:08
  • Olá, vou verificar e estudar, obrigado pela dica.
    sexta-feira, 19 de julho de 2019 15:09
  • Deleted
    sexta-feira, 19 de julho de 2019 15:26