Fórum Mais de um select executando #428059

05/11/2012

0

SQL Server

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

Responder

Posts

05/11/2012

Robson Alves

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
Responder

Gostei + 0

05/11/2012

Joao Gorgonha

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?
Responder

Gostei + 0

05/11/2012

Robson Alves

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?
Responder

Gostei + 0

05/11/2012

Joao Gorgonha

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.
Responder

Gostei + 0

05/11/2012

Robson Alves

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.
Responder

Gostei + 0

05/11/2012

Claudia Nogueira

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
Responder

Gostei + 0

12/11/2012

Joao Gorgonha

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

               

                        

                            

                                
                                Responder
                            


                            

                                
                                
Gostei + 0

                            

                        


                    



                


            

                

    
			
        

                       
            
        
        
    



            

            
            
            
        


    






        

        
				
		

		
				
					
			

				
Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

				

					Aceitar