Jump to content
ISREL LOUIS RAMOS

group by trazer hora zerada

Recommended Posts

Boa tarde,

Preciso do seguinte auxilio, fiz um select conforme mostra abaixo e agrupo no dia as horas, então conforme o movimento eu consigo saber as montante de movimentação por hora.

Exemplo:

00 = 1000

01 = 1500

02 = 2000

O problema está quando eu não obtive movimento na hora, ou seja, como usei group by, quando eu não tenho movimento ele não me trás nenhum resultado e na verdade para comparativos eu preciso que traga zero.

Hoje está assim, exemplo:

00 = 1000

01 = 1500

02 = 2000

05 = 1000

E eu precisava que trouxesse esse acumulado, assim:

00 = 1000

01 = 1500

02 = 2000

03 = 0

04 = 0

05 = 1000

Lembrando que como eu uso o sysdate e quero acompanhar graficamente as horas, eu não queria que ele preenchesse as demais horas, que ainda não passaram, exemplo agora são 15:50h e ele colocasse zero para o horário das 17,18,19,20, etc.

É possível?

select
APT.DT_SAIDA,
to_char(APT.HR_SAIDA, 'hh24') AS HORA,
SUM(APT.QT_LIQUIDO / 1000)AS CANA_ENTREGUE,
COUNT(*) AS CARGAS
from
PIMSPRD.APT_CARGAS APT, PIMSPRD.UPNIVEL3 UP3
where
APT.DT_SAIDA = TO_DATE(sysdate ,'DD/MM/YYYY')
and UP3.CD_SAFRA = APT.CD_SAFRA
and UP3.CD_UPNIVEL1 = APT.CD_UPNIVEL1
and UP3.CD_UPNIVEL2 = APT.CD_UPNIVEL2
and UP3.CD_UPNIVEL3 = APT.CD_UPNIVEL3
and ((APT.CD_UNID_IND = 1))
group by APT.DT_SAIDA, to_char(APT.HR_SAIDA,'hh24')
order by APT.DT_SAIDA, to_char(APT.HR_SAIDA,'hh24')

Share this post


Link to post
Share on other sites

Boa tarde,

Tenta desta forma:

Select trunc(sysdate) DT_SAIDA, l.HORA, SUM(A.QT_LIQUIDO / 1000) AS CANA_ENTREGUE,
COUNT(*) AS CARGAS
From ( select APT.DT_SAIDA, to_char(APT.HR_SAIDA, 'hh24') HORA, APT.QT_LIQUIDO
from PIMSPRD.APT_CARGAS APT, PIMSPRD.UPNIVEL3 UP3, (select level FROM dual CONNECT BY LEVEL <= 24) l;
where APT.DT_SAIDA = trunc(sysdate)
and UP3.CD_SAFRA = APT.CD_SAFRA
and UP3.CD_UPNIVEL1 = APT.CD_UPNIVEL1
and UP3.CD_UPNIVEL2 = APT.CD_UPNIVEL2
and UP3.CD_UPNIVEL3 = APT.CD_UPNIVEL3
and ((APT.CD_UNID_IND = 1)) ) a,
( select level hora FROM dual CONNECT BY LEVEL <= 24) l
where a.hora(+) = l.hora
group by trunc(sysdate), l.HORA
order by l.hora;
Não fiz o teste, mas a solução é mais ou menos essa.

Share this post


Link to post
Share on other sites

Bom dia amigo,

Então, eu fiz uma logica diferente, usando os seus conceitos:

select hr1.*, hr2.*
from
( Select to_char(level - 1) as hora
FROM dual
CONNECT BY LEVEL <= 24 ) hr1 ,
(
select
APT.DT_SAIDA,
to_number(to_char(APT.HR_SAIDA, 'hh24')) AS HORA,
SUM(APT.QT_LIQUIDO / 1000)AS CANA_ENTREGUE,
COUNT(*) AS CARGAS
from
PIMSPRD.APT_CARGAS APT, PIMSPRD.UPNIVEL3 UP3
where
APT.DT_SAIDA = TO_DATE('27/10/2016','DD/MM/YYYY')
and UP3.CD_SAFRA = APT.CD_SAFRA
and UP3.CD_UPNIVEL1 = APT.CD_UPNIVEL1
and UP3.CD_UPNIVEL2 = APT.CD_UPNIVEL2
and UP3.CD_UPNIVEL3 = APT.CD_UPNIVEL3
and ((APT.CD_UNID_IND = 1))
group by APT.DT_SAIDA, to_char(APT.HR_SAIDA,'hh24')
order by APT.DT_SAIDA, to_char(APT.HR_SAIDA,'hh24') ) hr2
where hr1.hora = hr2.hora(+)
order by hr1.hora
Muito obrigado.

Share this post


Link to post
Share on other sites

Terminei a query, alterei para que ela fosse number para melhorar no order by.

Anterior estava char.

Segue:

select hr1.*, hr2.*
from
( Select to_number(level - 1) as hora
FROM dual
CONNECT BY LEVEL <= 24 ) hr1 ,
(
select
APT.DT_SAIDA,
to_number(to_char(APT.HR_SAIDA, 'hh24')) AS HORA,
SUM(APT.QT_LIQUIDO / 1000)AS CANA_ENTREGUE,
COUNT(*) AS CARGAS
from
PIMSPRD.APT_CARGAS APT, PIMSPRD.UPNIVEL3 UP3
where
APT.DT_SAIDA = TO_DATE('27/10/2016','DD/MM/YYYY')
and UP3.CD_SAFRA = APT.CD_SAFRA
and UP3.CD_UPNIVEL1 = APT.CD_UPNIVEL1
and UP3.CD_UPNIVEL2 = APT.CD_UPNIVEL2
and UP3.CD_UPNIVEL3 = APT.CD_UPNIVEL3
and ((APT.CD_UNID_IND = 1))
group by APT.DT_SAIDA, to_char(APT.HR_SAIDA,'hh24')
order by APT.DT_SAIDA, to_char(APT.HR_SAIDA,'hh24') ) hr2
where to_number(trunc(hr1.hora)) = hr2.hora(+)
order by hr1.hora
Obrigado.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

Ao usar o fórum, você concorda com nossos Terms of Use.