Usuário com melhor resposta
Converter colunas em linhas com ajustes de ordenação

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:
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 serveDeixo 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]
- Editado Junior Galvão - MVPMVP quinta-feira, 18 de julho de 2019 14:30 Atualização do conteúdo da resposta
-
-
-
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.
-
-