CODEWITHSUNDEEP
sqloracle-database
Wesller Hack
Wesller Hack

Reputation: 1

Count of consecutive days ORACLE SQL

I need help with a query where a need to count de consecutive days like this

select  
  a.numcad, a.datapu , f.datapu , nvl(to_char(f.datapu, 'DD'),0)dia, 
  
    row_number() over (partition by a.numcad, f.datapu order by f.datapu)particao

from 
 ronda.r066apu a 
         left join (select t.numcad, t.numemp, t.datacc, t.datapu
                      from ronda.r070acc t
                      where t.datacc >= '21/01/2022'
                      and t.datacc <= trunc(sysdate)
                      group by t.numcad, t.numemp, t.datacc, t.datapu)f
          on a.numemp = f.numemp
          and a.numcad = f.numcad
          and a.datapu = f.datapu
 
where a.numcad = 2675
and A.DATAPU >= '21/01/2022'
and A.DATAPU <= trunc(sysdate)

group by a.numcad,  a.datapu, f.datapu, f.datacc
order by a.datapu

result is

enter image description here

between 24/01/2022 and 04/02/2022 is 12 days i need know this count , but i will ways get the '21/mes/year'

Upvotes: 0

Views: 75

Answers (1)

Diego Lobo
Diego Lobo

Reputation: 466

You can try:

SELECT TO_DATE('2022-01-24', 'YYYY-MM-DD') -  
   TO_DATE('2022-02-04', 'YYYY-MM-DD') 
 FROM   dual

This returns 21, for example...

Upvotes: 0

Related Questions