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

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

Sem problemas Alexandre! Acredito que toda ajuda seja bem vinda.

---

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

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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

Answer 9

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)?

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

Era exatamente disso que eu precisava. Muito Obrigada!!! ^ ^