Mais de um select executando

SQL Server

05/11/2012

Boa pessoal, preciso exibir 3 colunas, porem cada uma é bem distinta, segue abaixo o comando de cada uma:
O retorno do select teria que ser algo como DATA | DESJEJUM | ALMOÇO | JANTA.
Essa consulta vou por no Excel, onde por tabela dinamica vai me trazer o somatorio de cada uma.
Grato pela ajuda.

select CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) AS Data, count(mov_datahora) AS DESJEJUM from log_credencial
where mov_datahora between '20121001' and '20121016'
and mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) > '05:00'
AND mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) < '07:30'
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 '20121001' and '20121016'
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 '20121001' and '20121016'
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:30'
and eqpi_numero = 2
group by CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime)
Joao Gorgonha

Joao Gorgonha

Curtidas 0

Respostas

Robson Alves

Robson Alves

05/11/2012

Vocè pode fazer isso utilizando Case.

select
case 
   when tab1.col1 > '05:00' and tab2.col1 < '07:30'
     then 'Desejejum'
   when tab1.col1 = 1 and tab2.col1 = 1
     then 'Almoço'
   when tab1.col1 = 2
     then 'Jantar'
end as Refeicao
GOSTEI 0
Joao Gorgonha

Joao Gorgonha

05/11/2012

Entao cara, mas nao é condição, tem que vim tudo.
A instruçao no final tem que trazer:

DATA | DESJEJUM | ALMOÇO | JANTAR
01/10/2012 | 58 | 520 | 120

Saco?
GOSTEI 0
Robson Alves

Robson Alves

05/11/2012

Entao cara, mas nao é condição, tem que vim tudo.
A instruçao no final tem que trazer:

DATA | DESJEJUM | ALMOÇO | JANTAR
01/10/2012 | 58 | 520 | 120

Saco?



Sim agora entendi, eu penso em subquerys, o jeito mais rápido!

Já tentou isso?
GOSTEI 0
Joao Gorgonha

Joao Gorgonha

05/11/2012

Entao, tentar nao, pq nao sei, ate catei uns comandos na net, mas nao desenrolei.
Sei mais ou menos que tenho que encadear os SELECT, porem nao funcionou.
GOSTEI 0
Robson Alves

Robson Alves

05/11/2012

Entao, tentar nao, pq nao sei, ate catei uns comandos na net, mas nao desenrolei.
Sei mais ou menos que tenho que encadear os SELECT, porem nao funcionou.



To indo pra casa então vou passar um link com exemplos:


SELECT P.PRODUCTNAME,
(SELECT CATEGORYNAME FROM CATEGORIES WHERE CATEGORYID = P.CATEGORYID)
FROM PRODUCTS P



http://sqlservercodebook.blogspot.com.br/2008/03/sql-subselect-subqueries.html

Eu particularmente evito sub querys, dependendo do caso amarra a query, no seu caso, a query está bem específica (Where bem declarado) então não acho que tenha perca.

Mais tarde estou de volta.
GOSTEI 0
Claudia Nogueira

Claudia Nogueira

05/11/2012

Fiz aqui, mas pode ter erro de digitação, pois não tenho como testar sem a tabela:

SELECT lc.data, lc1.desjejum, lc2.almoco, lc3.jantar
  FROM (SELECT CAST(FLOOR(CAST(lc.mov_datahora AS float)) AS datetime) DATA
          FROM log_credencial lc
         WHERE (mov_datahora between '20121001' and '20121016')
           AND (eqpi_numero = 2)
           AND (((mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) > '05:00')
             AND (mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) < '07:30'))
             OR ((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'))
             OR ((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:30')))
        GROUP BY 1) lc,
       (SELECT count(lc1.mov_datahora) desjejum 
          FROM log_credencial lc1
         WHERE (lc.data = CAST(FLOOR(CAST(lc1.mov_datahora AS float))))
           AND (lc1.mov_datahora - CAST(FLOOR(CAST(lc1.mov_datahora AS float)) AS datetime) > '05:00')
           AND (lc1.mov_datahora - CAST(FLOOR(CAST(lc1.mov_datahora AS float)) AS datetime) < '07:30')
           AND (lc1.eqpi_numero = 2)) lc1,
       (SELECT count(lc2.mov_datahora) almoco 
          FROM log_credencial lc2
         WHERE (lc.data = CAST(FLOOR(CAST(lc2.mov_datahora AS float))))
           AND (lc2.mov_datahora - CAST(FLOOR(CAST(lc2.mov_datahora AS float)) AS datetime) > '10:30')
           AND (lc2.mov_datahora - CAST(FLOOR(CAST(lc2.mov_datahora AS float)) AS datetime) < '14:00')
           AND (lc2.eqpi_numero = 2)) lc2,
       (SELECT count(lc3.mov_datahora) jantar 
          FROM log_credencial lc3
         WHERE (lc.data = CAST(FLOOR(CAST(lc3.mov_datahora AS float))))
           AND (lc3.mov_datahora - CAST(FLOOR(CAST(lc3.mov_datahora AS float)) AS datetime) > '18:00')
           AND (lc3.mov_datahora - CAST(FLOOR(CAST(lc3.mov_datahora AS float)) AS datetime) < '20:30')
           AND (lc3.eqpi_numero = 2)) lc3
GOSTEI 0
Joao Gorgonha

Joao Gorgonha

05/11/2012

Boa Claudia,
Desculpe pela demora, mas so voltei ao trabalho hoje.
Entao, rodei sua sentança aqui, e esta dando erro nos CAST, das linhas 15, 21 e 27. Entao preenchi como AS datetime.
Porem ao executar esta dando erro no group by.

Msg 164, Level 15, State 1, Line 1
Each GROUP BY expression must contain at least one column that is not an outer reference.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "lc.data" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "lc.data" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "lc.data" could not be bound.




Fiz aqui, mas pode ter erro de digitação, pois não tenho como testar sem a tabela:

SELECT lc.data, lc1.desjejum, lc2.almoco, lc3.jantar
  FROM (SELECT CAST(FLOOR(CAST(lc.mov_datahora AS float)) AS datetime) DATA
          FROM log_credencial lc
         WHERE (mov_datahora between '20121001' and '20121016')
           AND (eqpi_numero = 2)
           AND (((mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) > '05:00')
             AND (mov_datahora - CAST(FLOOR(CAST(mov_datahora AS float)) AS datetime) < '07:30'))
             OR ((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'))
             OR ((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:30')))
        GROUP BY 1) lc,
       (SELECT count(lc1.mov_datahora) desjejum 
          FROM log_credencial lc1
         WHERE (lc.data = CAST(FLOOR(CAST(lc1.mov_datahora AS float))))
           AND (lc1.mov_datahora - CAST(FLOOR(CAST(lc1.mov_datahora AS float)) AS datetime) > '05:00')
           AND (lc1.mov_datahora - CAST(FLOOR(CAST(lc1.mov_datahora AS float)) AS datetime) < '07:30')
           AND (lc1.eqpi_numero = 2)) lc1,
       (SELECT count(lc2.mov_datahora) almoco 
          FROM log_credencial lc2
         WHERE (lc.data = CAST(FLOOR(CAST(lc2.mov_datahora AS float))))
           AND (lc2.mov_datahora - CAST(FLOOR(CAST(lc2.mov_datahora AS float)) AS datetime) > '10:30')
           AND (lc2.mov_datahora - CAST(FLOOR(CAST(lc2.mov_datahora AS float)) AS datetime) < '14:00')
           AND (lc2.eqpi_numero = 2)) lc2,
       (SELECT count(lc3.mov_datahora) jantar 
          FROM log_credencial lc3
         WHERE (lc.data = CAST(FLOOR(CAST(lc3.mov_datahora AS float))))
           AND (lc3.mov_datahora - CAST(FLOOR(CAST(lc3.mov_datahora AS float)) AS datetime) > '18:00')
           AND (lc3.mov_datahora - CAST(FLOOR(CAST(lc3.mov_datahora AS float)) AS datetime) < '20:30')
           AND (lc3.eqpi_numero = 2)) lc3


                

                
GOSTEI 0

             

             

        

            POSTAR