Mais de um select executando
05/11/2012
0
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
Posts
05/11/2012
Robson Alves
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
05/11/2012
Joao Gorgonha
A instruçao no final tem que trazer:
DATA | DESJEJUM | ALMOÇO | JANTAR
01/10/2012 | 58 | 520 | 120
Saco?
05/11/2012
Robson Alves
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?
05/11/2012
Joao Gorgonha
Sei mais ou menos que tenho que encadear os SELECT, porem nao funcionou.
05/11/2012
Robson Alves
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.
05/11/2012
Claudia Nogueira
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
12/11/2012
Joao Gorgonha
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.
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
Clique aqui para fazer login e interagir na Comunidade :)