none
Ajuda com Union RRS feed

  • Pergunta

  • 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


    terça-feira, 29 de abril de 2014 17:03

Respostas

  • 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

    terça-feira, 29 de abril de 2014 17:24

Todas as Respostas

  • 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

    terça-feira, 29 de abril de 2014 17:24
  • 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
    Email foxelv@hotmail.com

    terça-feira, 29 de abril de 2014 17:29
  • 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

    terça-feira, 29 de abril de 2014 17:50
  • 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

    terça-feira, 29 de abril de 2014 19:20
  • 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

    quarta-feira, 30 de abril de 2014 13:38