Usamos cookies para medir audiência e melhorar sua experiência. Você pode aceitar ou recusar a qualquer momento. Veja sobre o iMasters.
Bom pessoal,
Tenho tres consultas distintas que so muda a faixa de hora, porem quero que ela seja um unico select, exemplo:
Data | Cafe | Almoço | Jantar
21/12/12 | 15 | 62 | 36
Esses sao os comandos separados:
select CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) AS Data, count(mov_datahora) AS CAFE from log_credencial
where mov_datahora between '20121201' and '20121216'
and mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) > '05:30'
AND mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) < '08:00'
and eqpi_numero = 2
group by CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime)
select CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) AS Data, count(mov_datahora) AS ALMOÇO from log_credencial
where mov_datahora between '20121201' and '20121216'
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)
select CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) AS Data, count(mov_datahora) AS JANTAR from log_credencial
where mov_datahora between '20121201' and '20121216'
and mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) > '18:00'
AND mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) < '20:00'
and eqpi_numero = 2
group by CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime)
Alguem pode me ajudar?
Boa cara,
Desculpa a demora para responder, mas essa soluçao nao vai, ja testei.
select sum(case when CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) < '12:00'
then 1 else 0 end) cafe,
sum(case when CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) between '12:00' and '17:59'
then 1 else 0 end) almoco,
sum(case when CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) >= '18:00'
then 1 else 0 end) jantar
from log_credencial
where mov_datahora between '20121201' and '20121216'
and eqpi_numero = 2UNION com PIVOT
Vero , sqlServer tem o PIVOT.
Veja se ajuda
---case
http://forum.imasters.com.br/topic/475519-select-sql/page__p__1889501__hl__%2Bcase+%2Bsum__fromsearch__1#entry1889501
http://forum.imasters.com.br/topic/462399-count-com-condicao/page__p__1840894__hl__%2Bcase+%2Bsum__fromsearch__1#entry1840894
http://forum.imasters.com.br/topic/454617-sql-composto/page__p__1798833__hl__%2Bcase+%2Bsum__fromsearch__1#entry1798833
----union
http://forum.imasters.com.br/topic/479994-select-multiplos/page__p__1907435__hl__%2Bunion+%2Bselect__fromsearch__1#entry1907435