Select encadeado

[jramos 0]

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?

[Motta 645]

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

[jramos 0]

Boa cara,

Desculpa a demora para responder, mas essa soluçao nao vai, ja testei.

[Motta 645]

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 = 2

[advaldomesquita 93]

UNION com PIVOT

[Motta 645]

Vero , sqlServer tem o PIVOT.