Agrupar por data
05/11/2012
0
Porem a tabela esta em datetime, e esta agrupanto por minuto.
Segue abaixo minha consulta, se alguem puder ajudar agradeço:
select l.mov_datahora, count(l.mov_datahora) from log_credencial l JOIN pessoas p ON l.pes_numero = p.pes_numero where l.mov_datahora between '20121001' and '20121015' and l.mov_datahora - CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) > '10:30' AND l.mov_datahora - CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) < '14:00' and l.eqpi_numero = 2 group by l.mov_datahora order by l.mov_datahora
Joao Gorgonha
Posts
05/11/2012
Claudia Nogueira
select CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) AS mov_datahora, count(l.mov_datahora) from log_credencial l JOIN pessoas p ON l.pes_numero = p.pes_numero where l.mov_datahora between '20121001' and '20121015' and l.mov_datahora - CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) > '10:30' AND l.mov_datahora - CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) < '14:00' and l.eqpi_numero = 2 group by 1 order by 1
05/11/2012
Joao Gorgonha
01/10/2012 00:00:00 1 01/10/2012 00:00:00 5 01/10/2012 00:00:00 7 01/10/2012 00:00:00 4 01/10/2012 00:00:00 1 01/10/2012 00:00:00 4 01/10/2012 00:00:00 2 01/10/2012 00:00:00 1
05/11/2012
Claudia Nogueira
Tenta assim:
select CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) AS mov_datahora, count(CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime)) from log_credencial l JOIN pessoas p ON l.pes_numero = p.pes_numero where l.mov_datahora between '20121001' and '20121015' and l.mov_datahora - CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) > '10:30' AND l.mov_datahora - CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) < '14:00' and l.eqpi_numero = 2 group by 1 order by 1
05/11/2012
Claudia Nogueira
Tenta assim:
select CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) AS mov_datahora, count(CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime)) from log_credencial l JOIN pessoas p ON l.pes_numero = p.pes_numero where l.mov_datahora between '20121001' and '20121015' and l.mov_datahora - CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) > '10:30' AND l.mov_datahora - CAST(FLOOR(CAST(l.mov_datahora AS float)) AS datetime) < '14:00' and l.eqpi_numero = 2 group by 1 order by 1
05/11/2012
Joao Gorgonha
Na verdade faltou por a instruçao no Group By, ficou assim:
select CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) AS mov_datahora, count(mov_datahora) from log_credencial where mov_datahora between '20121001' and '20121015' and mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) > '10:30' AND mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) < '14:00' and eqpi_numero = 2 group by CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) order by mov_datahora
Muito obrigado.
05/11/2012
Claudia Nogueira
Quando você usa o número do campo ele já pega automaticamente o que estava na sentença SQL, por isso não coloquei no group nem no order.
Dá no mesmo.
Na verdade faltou por a instruçao no Group By, ficou assim:
select CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) AS mov_datahora, count(mov_datahora) from log_credencial where mov_datahora between '20121001' and '20121015' and mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) > '10:30' AND mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) < '14:00' and eqpi_numero = 2 group by CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) order by mov_datahora
Muito obrigado.
Clique aqui para fazer login e interagir na Comunidade :)
