Ajuda com Union

Question

Amigos, bom dia. 

Estou rodando uma query que unem 3 tabelas. Uma de FALTAS, uma de ATRASOS e outra de HORAS EXTRAS. 

Meu problema é o seguinte: vamos supor que o Funcionario1 tem falta e atraso. Nesse caso, criam-se 2 linhas pra ele. Uma pra cada modalidade. Teria como eu fazer com que as celulas fossem mescladas. Vejam meu resultado atual e a query:

DECLARE @MesCompetencia INT = 5
DECLARE @AnoCompetencia INT = 2014

SELECT 
c.nome AS COLABORADOR,
SUM((DATEDIFF(DAY, periodo_de,periodo_ate) + 1)) AS FALTAS,
SUM(dias_dsr) AS DSRFALTAS,
'' AS ATRASO,
'' AS [100PC],
'' AS [80PC],
'' AS [60PC],
'' AS [50PC],
'' AS ADNOTURNO,
'' AS DSRHORAEXTRA
FROM 
afastamento a
INNER JOIN tb_colaborador c ON a.id_colaborador = c.id_colaborador
INNER JOIN admissao ad on a.id_colaborador = ad.id_colaborador
WHERE 
bool_descontardias = 1
AND
((DATEPART(MONTH, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN 12 ELSE @MesCompetencia - 2 END)
AND 
(DATEPART(YEAR, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN @AnoCompetencia - 1 ELSE @AnoCompetencia END))
OR 
(mes_desconto = @MesCompetencia AND ano_desconto = @AnoCompetencia)
AND
a.atraso is not null
AND 
ad.status <> 'inativo'
GROUP BY c.nome

UNION 

SELECT  
c.nome AS COLABORADOR,
'' AS FALTAS,
'' AS DSRFALTAS,
CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND, 0, CAST(a.atraso AS DATETIME))),0) AS TIME(0)) AS ATRASO ,
'' AS [100PC],
'' AS [80PC],
'' AS [60PC],
'' AS [50PC],
'' AS ADNOTURNO,
'' AS DSRHORAEXTRA
FROM afastamento a
INNER JOIN tb_colaborador c ON a.id_colaborador = c.id_colaborador
INNER JOIN admissao ad on a.id_colaborador = ad.id_colaborador
WHERE 
id_motivoafastamento = 19 
AND
((DATEPART(MONTH, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN 12 ELSE @MesCompetencia - 2 END)
AND 
(DATEPART(YEAR, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN @AnoCompetencia - 1 ELSE @AnoCompetencia END))
OR 
(mes_desconto = @MesCompetencia AND ano_desconto = @AnoCompetencia)
AND
a.atraso is not null
AND 
ad.status <> 'inativo'
GROUP BY c.nome

UNION

SELECT  
c.nome AS COLABORADOR,
'' AS FALTAS,
'' AS DSRFALTAS,
'' AS ATRASO,
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_100) + 60 * DATEPART(MINUTE, h.pct_100) + 3600 * DATEPART(HOUR, h.pct_100))), 's'))   AS [100PC],
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_80) + 60 * DATEPART(MINUTE, h.pct_80) + 3600 * DATEPART(HOUR, h.pct_80))), 's'))		AS [80PC],
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_60) + 60 * DATEPART(MINUTE, h.pct_60) + 3600 * DATEPART(HOUR, h.pct_60))), 's'))		AS [60PC],
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_50) + 60 * DATEPART(MINUTE, h.pct_50) + 3600 * DATEPART(HOUR, h.pct_50))), 's'))		AS [50PC],
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.adicional_noturno) + 60 * DATEPART(MINUTE, h.adicional_noturno) + 3600 * DATEPART(HOUR, h.adicional_noturno))), 's')) AS [ADNOTURNO],
SUM(h.dsr) AS DSR
FROM horas_extras h
INNER JOIN tb_colaborador c ON h.id_colaborador = c.id_colaborador
INNER JOIN admissao ad on h.id_colaborador = ad.id_colaborador
WHERE 
(mes_pagamento = @MesCompetencia AND ano_pagamento = @AnoCompetencia)
AND 
ad.status <> 'inativo'
GROUP BY c.nome
ORDER BY c.nome

O resultado atual é este:

Vejam o caso da Aline. Eu gostaria de mostra o conteúdo das 2 linhas em 1 só.

Desde já, obrigado :)

---

Leonardo D'Amato

Answer 1

Boa tarde Leonardo,

A idéia seria agrupar seu resultado atual em uma query mais externa. Segue ideia..

SELECT
	X.COLABORADOR,
	SUM(X.FALTAS) AS FALTAS,
	SUM(X.DSRFALTAS) AS DSRFALTAS,
	SUM(X.[100PC])   AS [100PC],
	SUM(X.[80PC])    AS [50PC],
	SUM(X.[60PC])    AS [60PC],
	SUM(X.[50PC])    AS [80PC]
FROM (	
		SELECT 
		c.nome AS COLABORADOR,
		SUM((DATEDIFF(DAY, periodo_de,periodo_ate) + 1)) AS FALTAS,
		SUM(dias_dsr) AS DSRFALTAS,
		'' AS ATRASO,
		'' AS [100PC],
		'' AS [80PC],
		'' AS [60PC],
		'' AS [50PC],
		'' AS ADNOTURNO,
		'' AS DSRHORAEXTRA
		FROM 
		afastamento a
		INNER JOIN tb_colaborador c ON a.id_colaborador = c.id_colaborador
		INNER JOIN admissao ad on a.id_colaborador = ad.id_colaborador
		WHERE 
		bool_descontardias = 1
		AND
		((DATEPART(MONTH, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN 12 ELSE @MesCompetencia - 2 END)
		AND 
		(DATEPART(YEAR, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN @AnoCompetencia - 1 ELSE @AnoCompetencia END))
		OR 
		(mes_desconto = @MesCompetencia AND ano_desconto = @AnoCompetencia)
		AND
		a.atraso is not null
		AND 
		ad.status <> 'inativo'
		GROUP BY c.nome

		UNION 

		SELECT  
		c.nome AS COLABORADOR,
		'' AS FALTAS,
		'' AS DSRFALTAS,
		CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND, 0, CAST(a.atraso AS DATETIME))),0) AS TIME(0)) AS ATRASO ,
		'' AS [100PC],
		'' AS [80PC],
		'' AS [60PC],
		'' AS [50PC],
		'' AS ADNOTURNO,
		'' AS DSRHORAEXTRA
		FROM afastamento a
		INNER JOIN tb_colaborador c ON a.id_colaborador = c.id_colaborador
		INNER JOIN admissao ad on a.id_colaborador = ad.id_colaborador
		WHERE 
		id_motivoafastamento = 19 
		AND
		((DATEPART(MONTH, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN 12 ELSE @MesCompetencia - 2 END)
		AND 
		(DATEPART(YEAR, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN @AnoCompetencia - 1 ELSE @AnoCompetencia END))
		OR 
		(mes_desconto = @MesCompetencia AND ano_desconto = @AnoCompetencia)
		AND
		a.atraso is not null
		AND 
		ad.status <> 'inativo'
		GROUP BY c.nome

		UNION

		SELECT  
		c.nome AS COLABORADOR,
		'' AS FALTAS,
		'' AS DSRFALTAS,
		'' AS ATRASO,
		(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_100) + 60 * DATEPART(MINUTE, h.pct_100) + 3600 * DATEPART(HOUR, h.pct_100))), 's'))   AS [100PC],
		(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_80) + 60 * DATEPART(MINUTE, h.pct_80) + 3600 * DATEPART(HOUR, h.pct_80))), 's'))		AS [80PC],
		(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_60) + 60 * DATEPART(MINUTE, h.pct_60) + 3600 * DATEPART(HOUR, h.pct_60))), 's'))		AS [60PC],
		(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_50) + 60 * DATEPART(MINUTE, h.pct_50) + 3600 * DATEPART(HOUR, h.pct_50))), 's'))		AS [50PC],
		(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.adicional_noturno) + 60 * DATEPART(MINUTE, h.adicional_noturno) + 3600 * DATEPART(HOUR, h.adicional_noturno))), 's')) AS [ADNOTURNO],
		SUM(h.dsr) AS DSR
		FROM horas_extras h
		INNER JOIN tb_colaborador c ON h.id_colaborador = c.id_colaborador
		INNER JOIN admissao ad on h.id_colaborador = ad.id_colaborador
		WHERE 
		(mes_pagamento = @MesCompetencia AND ano_pagamento = @AnoCompetencia)
		AND 
		ad.status <> 'inativo'
		GROUP BY c.nome
		ORDER BY c.nome
	)X
	GROUP BY X.COLABORADOR

At.
Rafael

Answer 2

Você pode adicionar o resultado para dentro de uma temp e agrupar pelo colaborador.

DECLARE @MesCompetencia INT = 5
DECLARE @AnoCompetencia INT = 2014

SELECT 
c.nome AS COLABORADOR,
SUM((DATEDIFF(DAY, periodo_de,periodo_ate) + 1)) AS FALTAS,
SUM(dias_dsr) AS DSRFALTAS,
'' AS ATRASO,
'' AS [100PC],
'' AS [80PC],
'' AS [60PC],
'' AS [50PC],
'' AS ADNOTURNO,
'' AS DSRHORAEXTRA
into #temp_colaborador
FROM 
afastamento a
INNER JOIN tb_colaborador c ON a.id_colaborador = c.id_colaborador
INNER JOIN admissao ad on a.id_colaborador = ad.id_colaborador
WHERE 
bool_descontardias = 1
AND
((DATEPART(MONTH, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN 12 ELSE @MesCompetencia - 2 END)
AND 
(DATEPART(YEAR, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN @AnoCompetencia - 1 ELSE @AnoCompetencia END))
OR 
(mes_desconto = @MesCompetencia AND ano_desconto = @AnoCompetencia)
AND
a.atraso is not null
AND 
ad.status <> 'inativo'
GROUP BY c.nome

UNION 

SELECT  
c.nome AS COLABORADOR,
'' AS FALTAS,
'' AS DSRFALTAS,
CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND, 0, CAST(a.atraso AS DATETIME))),0) AS TIME(0)) AS ATRASO ,
'' AS [100PC],
'' AS [80PC],
'' AS [60PC],
'' AS [50PC],
'' AS ADNOTURNO,
'' AS DSRHORAEXTRA
FROM afastamento a
INNER JOIN tb_colaborador c ON a.id_colaborador = c.id_colaborador
INNER JOIN admissao ad on a.id_colaborador = ad.id_colaborador
WHERE 
id_motivoafastamento = 19 
AND
((DATEPART(MONTH, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN 12 ELSE @MesCompetencia - 2 END)
AND 
(DATEPART(YEAR, periodo_de) = CASE WHEN @MesCompetencia = 1 THEN @AnoCompetencia - 1 ELSE @AnoCompetencia END))
OR 
(mes_desconto = @MesCompetencia AND ano_desconto = @AnoCompetencia)
AND
a.atraso is not null
AND 
ad.status <> 'inativo'
GROUP BY c.nome

UNION

SELECT  
c.nome AS COLABORADOR,
'' AS FALTAS,
'' AS DSRFALTAS,
'' AS ATRASO,
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_100) + 60 * DATEPART(MINUTE, h.pct_100) + 3600 * DATEPART(HOUR, h.pct_100))), 's'))   AS [100PC],
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_80) + 60 * DATEPART(MINUTE, h.pct_80) + 3600 * DATEPART(HOUR, h.pct_80))), 's'))		AS [80PC],
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_60) + 60 * DATEPART(MINUTE, h.pct_60) + 3600 * DATEPART(HOUR, h.pct_60))), 's'))		AS [60PC],
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.pct_50) + 60 * DATEPART(MINUTE, h.pct_50) + 3600 * DATEPART(HOUR, h.pct_50))), 's'))		AS [50PC],
(select dbo.ConvertTimeToHHMMSS((select total_second = SUM(DATEPART(SECOND, h.adicional_noturno) + 60 * DATEPART(MINUTE, h.adicional_noturno) + 3600 * DATEPART(HOUR, h.adicional_noturno))), 's')) AS [ADNOTURNO],
SUM(h.dsr) AS DSR
FROM horas_extras h
INNER JOIN tb_colaborador c ON h.id_colaborador = c.id_colaborador
INNER JOIN admissao ad on h.id_colaborador = ad.id_colaborador
WHERE 
(mes_pagamento = @MesCompetencia AND ano_pagamento = @AnoCompetencia)
AND 
ad.status <> 'inativo'
GROUP BY c.nome
ORDER BY c.nome

select 
COLABORADOR,
SUM(FALTAS),
.
.
.
.
from 
#temp_colaborador 
group by COLABORADOR

---

Elvis Rodrigues Analista Desenvolvedor .Net MCTS .Net Framework, SQL Server 2005, WPF, ASP NET [e-mail]

Answer 3

Pessoal, primeiramente muito grato pela resposta.

Porém, nas duas soluções em que vcs me ajudaram, está apresentando o mesmo erro no qual não estou conseguindo corrigir. Poderiam dar uma força?

Mensagem 245, Nível 16, Estado 1, Linha 87
Conversion failed when converting the varchar value '0:00:00.000' to data type int.

Valeu

---

Leonardo D'Amato

Answer 4

Leonardo,

Realmente. Sua query está restornando strings (horas e espaços). Acredito que você terá que converter o resultado final (da query mais externa) os dados para date e depois para time,  que é o seu objetivo.

ex:

SUM(CAST(CAST(X.[100PC] AS DATETIME) AS TIME))   AS [100PC],

At.
Rafael

Answer 5

Rafael, tive que fazer diversas modificacoes por conta de formatos e conversões, mas seu código foi a base para que eu resolvesse o problema. 

Muito obrigado, cara. 
Sucesso e um abraço.

---

Leonardo D'Amato