Converter colunas em linhas com ajustes de ordenação

Question

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:

Answer 1

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]

Answer 2

Deleted

Answer 3

Deleted

Answer 4

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.

Answer 5

Olá, vou verificar e estudar, obrigado pela dica.

Answer 6

Deleted