none
Uso das funções em um comando sem group by RRS feed

  • Pergunta

  • Boa tarde, eu estou montando um comando sql no qual eu preciso de retornar informações da tabela referente a um determinado mês, sendo que a tabela armazena os dados por dia. Uma das informações que eu preciso é a data da última vez que a pessoa bateu ponto na empresa. Para isso, eu estava utilizando a função max no atributo data, porém, como estou utilizando uma função dentro de um select com várias informações, o sql exige q eu use a função group by. Porém, se eu usar a função group by, ele não mais pega a última data da batida, e sim agrupa pela data em cada dia. Eu queria saber se há algum jeito de eu fazer essa selação da útlima data sem precisar de usar a cláusula group by. Desde já agradeço.

    Att,

    Jéssica.

    quarta-feira, 6 de fevereiro de 2013 20:23

Respostas

  • Tente executar o script abaixo utilizando o botão "New Query" do SQL Server Management Studio para ver se é obtido o resultado desejado:

    declare @Tabela2 table
    (codpessoa int, nome varchar(40));
    
    insert into @Tabela2 values
    (1, 'ABC'),
    (2, 'DEF'),
    (3, 'GHI');
    
    declare @Tabela table
    (codpessoa int, data datetime, qtdhorasbatida int, qtdhorasaprop int);
    
    insert into @Tabela values
    (1, '2013-02-10 13:00', 10, null),
    (1, '2013-02-12 10:00', 10, null),
    (1, '2013-02-11 12:00', 10, null),
    
    (2, '2013-02-20 10:00', 10, null),
    (2, '2013-02-21 08:00', 10, null),
    (2, '2013-02-22 09:00', 10, null),
    
    (3, '2013-02-20 19:00', 10, null),
    
    (1, '2013-02-01 13:00', null, 10),
    (1, '2013-02-03 13:00', null, 10),
    (1, '2013-02-02 13:00', null, 10),
    
    (2, '2013-02-16 13:00', null, 10),
    (2, '2013-02-17 13:00', null, 10),
    (2, '2013-02-18 13:00', null, 10);
    
    with teste_CTE As 
    ( 
        Select  
            t1.codpessoa 
           ,t2.nome 
           ,t1.data
           ,t1.qtdhorasaprop
           ,ROW_NUMBER() 
                OVER
                    (PARTITION BY t1.codpessoa 
                     ORDER BY case when t1.qtdhorasbatida is null then 2 else 1 end,
                                        t1.data DESC) as RowNumPonto
           ,ROW_NUMBER() 
                OVER
                    (PARTITION BY t1.codpessoa 
                     ORDER BY case when t1.qtdhorasaprop is null then 2 else 1 end,
                                        t1.data DESC) as RowNumAprop
        from @tabela t1
        inner join @tabela2 t2 on t1.codpessoa = t2.codpessoa
    )
    
    Select 
        p.codpessoa,
        p.nome,
        p.data as DataUltimaBatida,
        a.data as DataUltimaAprop
    from teste_CTE as p
    left join teste_CTE as a
        on a.codpessoa = p.codpessoa and
           a.RowNumAprop = 1 and
           a.qtdhorasaprop is not null
    where p.RowNumPonto = 1
    order by p.nome

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta Jéssica_Lau quinta-feira, 14 de fevereiro de 2013 17:46
    quarta-feira, 13 de fevereiro de 2013 23:59

Todas as Respostas

  • Boa tarde,

    Confira uma questão semelhante a sua no tópico abaixo:

    http://social.msdn.microsoft.com/Forums/pt-BR/520/thread/3c52874d-7541-43e0-8de4-6a7cce80923a

    Em caso de dúvida é só postar.

    Espero que seja útil.


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 6 de fevereiro de 2013 20:34
  • Jessica, utilizando somente o group by sua query irá realmente agrupar a ultima data porem para todos os campos distintos, se voce for usar somente o nome e a ultima data funcionaria sem problemas.

    Se for necessario utilizar todos os campos, experimento utilizar o CTE, segue exemplo abaixo:

    Create table #temp (Nome varchar(100), data smalldatetime, Campo3 varchar(10))
    
    insert into #temp
    values ('Alexandre',GETDATE(),'teste'),
    ('Alexandre',GETDATE(),'teste2'),
    ('Alexandre',GETDATE()-1,'teste3'),
    ('Alexandre',GETDATE()-4,'teste4'),
    ('Alexandre',GETDATE()-5,'teste5'),
    ('Alexandre',GETDATE(),'teste6'),
    ('Alexandre',GETDATE() ,'teste7')
    
    /*
    Aqui retornaria varios valores duplicados
    select
    	NOme,
    	MAX(data) as data,
    	Campo3
    		from #temp
    group by Nome, campo3
    */
    
    -- Utilizando CTE retorna o ultimo registro de cada data
    WITH Temp_CTE 
    AS
    -- Define the CTE query.
    (
        SELECT Nome, max(data) as data, Campo3,ROW_NUMBER() OVER(PARTITION BY data ORDER BY data DESC) as RowNum
        FROM #temp
        group by Nome,data,Campo3
    )
    -- Define the outer query referencing the CTE name.
    SELECT Nome, max(data) as data, Campo3
    FROM Temp_CTE
    where RowNum = 1
    GROUP BY Nome, Campo3
    ORDER BY Nome, Campo3
    GO
    


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 6 de fevereiro de 2013 20:43
  • Desculpe amigo, estava montando o exemplo quando ainda não havia resposta, somente agora vi que voce ja havia respondido.

    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 6 de fevereiro de 2013 20:45
  • Sem problemas Alexandre! Acredito que toda ajuda seja bem vinda.


    Assinatura: http://www.imoveisemexposicao.com.br

    quinta-feira, 7 de fevereiro de 2013 00:11
  • Obrigada pelas respostas, porém eu ainda não consegui atinjir o resultado que estou precisando...

    A minha query ficou mais ou menos assim:

    with teste_CTE
    As ( Select  codpessoa 
    ,max(data) as 'data'
    ,nome 
    ,campo1
    ,campo2
    ,campo3
    ,campo4
    ,ROW_NUMBER() OVER(PARTITION BY dataORDER BY data DESC) as RowNum
    from tabela t1
    inner join tabela2 t2 on t1.cdopessoa = t2.codpessoa
    left join tabela3 on t2.campo1 = t3.campo2
    group by codpessoa 
    ,max(data) as 'data' 
    ,campo1
    ,campo2
    ,campo3
    ,campo4)
    
    Select *
    from teste_CTE
    order by nome
    

    Oq está acontecendo é q o resultado é retornado por dia, e o resultado que eu estou precisando deveria ser por mês. Por exemplo, se a pessoa bateu o ponto todos os dias do mês, em um mês de 30 dias, o resultado retornado deveria ser o último dia, o dia 30. Isso para cada pessoa da empresa.

    quinta-feira, 7 de fevereiro de 2013 12:40
  • Jessica, no ultimo select coloque mais uma condição para trazer somente o ultimo registro do rownum:

    Select *
    from teste_CTE
    where RowNum = 1
    order by nome
    


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 7 de fevereiro de 2013 12:45
  • Jessica, outra coisa que reparei, na sua query no CTE, na parte do group by retire o group by max(data), voce só tem que agrupar os campos que não tem funções agrupadoras:

    with teste_CTE
    As ( Select  codpessoa 
    ,max(data) as 'data'
    ,nome 
    ,campo1
    ,campo2
    ,campo3
    ,campo4
    ,ROW_NUMBER() OVER(PARTITION BY data ORDER BY data DESC) as RowNum
    from tabela t1
    inner join tabela2 t2 on t1.cdopessoa = t2.codpessoa
    left join tabela3 on t2.campo1 = t3.campo2
    group by codpessoa 
    ,nome
    ,campo1
    ,campo2
    ,campo3
    ,campo4)
    
    Select *
    from teste_CTE
    where RowNum = 1
    order by nome


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 7 de fevereiro de 2013 13:01
  • Jéssica,

    Experimente mais ou menos desta forma:

    with teste_CTE As 
    ( 
        Select  
            codpessoa 
           ,data
           ,nome 
           ,campo1
           ,campo2
           ,campo3
           ,campo4
           ,ROW_NUMBER() OVER(PARTITION BY codpessoa ORDER BY data DESC) as RowNum
        from tabela t1
        inner join tabela2 t2 on t1.cdopessoa = t2.codpessoa
        left join tabela3 on t2.campo1 = t3.campo2
    )
    
    Select *
    from teste_CTE
    where RowNum = 1
    order by nome

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    quinta-feira, 7 de fevereiro de 2013 13:03
  • Muito obrigada a todos, o PARTITION BY codpessoa resolveu o meu problema. Mas agora eu me deparei com o seguinte: como havia dito antes, eu preciso trazer qual foi a última batida dos funcionários da empresa.

    Na tabela que eu estou dando esse select, além de outras informações, ela tem a informação da quantidade de horas que o funcionário apropriou no sistema e a quantidade de batidas de ponto. No resultado do meu select, eu preciso tanto da data da última vez que a pessoa bateu o ponto, quanto a data da última vez que a pessoa apropriou no sistema. O campo a ser retornado no resultado é o mesmo (data), a única diferença é que enquanto a data da última batida do ponto  tem que ser retornada tomando como base o último registro de qtd de horas batidas, a data da última apropriação tem que ser retornada tomando como base o último registro de qtd de horas apropriadas.

    Como é possível eu inserir essa condição no meu select (se é possível rsrs)?

    sexta-feira, 8 de fevereiro de 2013 20:28
  • Como é possível identificar a linha com o ponto e a linha com a apropriação?

    Será necessário retornar mais de uma coluna da linha com o ponto e mais de uma coluna da linha com a apropriação?

    Acho facilitaria o entendimento da questão se você puder postar uma pequena amostra de dados desta tabela e o respectivo resultado desejado.


    Assinatura: http://www.imoveisemexposicao.com.br

    sexta-feira, 8 de fevereiro de 2013 21:51
  • Então, como eu disse, tanto o dia da batidade de ponto, quanto o dia da apropriação, são salvos no mesmo atributo da tabela, que é o atributo "data".

    O que eu teria de verificar para diferenciar se é um ou outro, no caso das batidas de ponto, seria um outro atributo, o "qtdhorasbatida", que no caso eu teria que olhar a última "data" na qual, na mesma linha correspondente, o atributo "qtdhorasbatida" não está nulo. A mesma coisa para a apropriação, porém o atributo referente a apropriação é o "qtdhorasaprop".

    Segue uma parte do resultado da query:

    AnoMês           Filial           Última data atualização             Obrigatório                    C. Custo     Chapa

    2012	12	NULL	2012-12-28 00:00:00.000	S		1466	0000287SP
    2012	12	NULL	2012-12-07 00:00:00.000	S		1934	0001200BH
    2012	12	NULL	2012-12-28 00:00:00.000	S		1837	0001434BH
    2012	12	NULL	2012-12-28 00:00:00.000	S		1493	0001311BH
    2012	12	NULL	2012-12-14 00:00:00.000	S		2745	0000252SP
    A última data que está sendo retornada, é a ultima data que foi registrada no atributo "data", porém eu teria que trazer duas últimas datas, uma tem que ser última na qual o atributo qtdhorasbatida não está nulo, e outra tem que ser a última na qual o atributo qtdhorasaprop não está nulo. Espero que tenha dado para entender... rs

    • Editado Jéssica_Lau quarta-feira, 13 de fevereiro de 2013 16:27
    quarta-feira, 13 de fevereiro de 2013 16:27
  • Considerando que será necessário retornar mais de uma coluna da linha com o ponto e mais de uma coluna da linha com a apropriação, experimente mais ou menos desta forma:

    with teste_CTE As 
    ( 
        Select  
            codpessoa 
           ,data
           ,nome 
           ,qtdhorasaprop
           ,campo1
           ,campo2
           ,campo3
           ,ROW_NUMBER() 
                OVER
                    (PARTITION BY codpessoa 
                     ORDER BY case when qtdhorasbatida is null then 2 else 1 end,
                                       data DESC) as RowNumPonto
           ,ROW_NUMBER() 
                OVER
                    (PARTITION BY codpessoa 
                     ORDER BY case when qtdhorasaprop is null then 2 else 1 end,
                                       data DESC) as RowNumAprop
        from tabela t1
        inner join tabela2 t2 on t1.cdopessoa = t2.codpessoa
        left join tabela3 on t2.campo1 = t3.campo2
    )
    
    Select p.*, a.*
    from teste_CTE as p
    left join teste_CTE as a
        on a.codpessoa = p.codpessoa and
           a.RowNumAprop = 1 and
           a.qtdhorasaprop is not null
    where p.RowNumPonto = 1
    order by p.nome


    Dependendo das colunas necessárias no resultado pode ser que seja possível uma query sem o self join.

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 13 de fevereiro de 2013 17:14
  • Gapimex, nesse caso, quando eu coloco o left join, o resultado foi retornado duplicado...

    E foi retornado apenas uma coluna "data" e eu preciso de duas colunas "data", uma para cada situação dessas citadas.  

    Desculpe tantas dúvidas, mas é pq eu só sei o básico mesmo rsrs

    quarta-feira, 13 de fevereiro de 2013 19:30
  • Tente executar o script abaixo utilizando o botão "New Query" do SQL Server Management Studio para ver se é obtido o resultado desejado:

    declare @Tabela2 table
    (codpessoa int, nome varchar(40));
    
    insert into @Tabela2 values
    (1, 'ABC'),
    (2, 'DEF'),
    (3, 'GHI');
    
    declare @Tabela table
    (codpessoa int, data datetime, qtdhorasbatida int, qtdhorasaprop int);
    
    insert into @Tabela values
    (1, '2013-02-10 13:00', 10, null),
    (1, '2013-02-12 10:00', 10, null),
    (1, '2013-02-11 12:00', 10, null),
    
    (2, '2013-02-20 10:00', 10, null),
    (2, '2013-02-21 08:00', 10, null),
    (2, '2013-02-22 09:00', 10, null),
    
    (3, '2013-02-20 19:00', 10, null),
    
    (1, '2013-02-01 13:00', null, 10),
    (1, '2013-02-03 13:00', null, 10),
    (1, '2013-02-02 13:00', null, 10),
    
    (2, '2013-02-16 13:00', null, 10),
    (2, '2013-02-17 13:00', null, 10),
    (2, '2013-02-18 13:00', null, 10);
    
    with teste_CTE As 
    ( 
        Select  
            t1.codpessoa 
           ,t2.nome 
           ,t1.data
           ,t1.qtdhorasaprop
           ,ROW_NUMBER() 
                OVER
                    (PARTITION BY t1.codpessoa 
                     ORDER BY case when t1.qtdhorasbatida is null then 2 else 1 end,
                                        t1.data DESC) as RowNumPonto
           ,ROW_NUMBER() 
                OVER
                    (PARTITION BY t1.codpessoa 
                     ORDER BY case when t1.qtdhorasaprop is null then 2 else 1 end,
                                        t1.data DESC) as RowNumAprop
        from @tabela t1
        inner join @tabela2 t2 on t1.codpessoa = t2.codpessoa
    )
    
    Select 
        p.codpessoa,
        p.nome,
        p.data as DataUltimaBatida,
        a.data as DataUltimaAprop
    from teste_CTE as p
    left join teste_CTE as a
        on a.codpessoa = p.codpessoa and
           a.RowNumAprop = 1 and
           a.qtdhorasaprop is not null
    where p.RowNumPonto = 1
    order by p.nome

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta Jéssica_Lau quinta-feira, 14 de fevereiro de 2013 17:46
    quarta-feira, 13 de fevereiro de 2013 23:59
  • Era exatamente disso que eu precisava. Muito Obrigada!!! ^ ^
    quinta-feira, 14 de fevereiro de 2013 17:47