missing hours in a given time interval

Hi

I have  column called  time_interval  the format is 'dd/mm/yyyy hh24:mi:ss'
every  day  the data will be coming in , for  every hour, min, sec
some days the data will be missing ..
here i want to write select statement to find the missing hours in a give day .
or missing  hours for a given interval  period of month
 like i want to find the missing hours  
for the month '01-Feb-2007 :00:00:00 to 28-Feb-2007 :23:00:00'
so it should display all the missing hours of that  particular month.
Please assist ,

Thanks in Advance
Raj
nrajasekhar7Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
if your data is actually of date type
then you can skip the conversion on the NOT IN clause
SELECT hr
  FROM (SELECT TO_DATE('01-Feb-2007 :00:00:00', 'dd-mon-yyyy :hh24:mi:ss')
               + (ROWNUM - 1) / 24
                   hr
          FROM all_objects -- use any big table, something with at least as many rows as hours in your range
         WHERE TO_DATE('01-Feb-2007 :00:00:00', 'dd-mon-yyyy :hh24:mi:ss')
               + (ROWNUM - 1) / 24 <=
                   TO_DATE(
                       '28-Feb-2007 :23:00:00',
                       'dd-mon-yyyy :hh24:mi:ss'
                   ))
 WHERE hr NOT IN (SELECT TRUNC(time_interval, 'hh') FROM your_table);

Open in new window

0
 
sdstuberCommented:
so  time_interval is a string,  not a date?

The reason I ask is you say your data has a format,  dates don't have formats, only strings do.

assuming your data is strings then this should work
SELECT hr
  FROM (SELECT TO_DATE('01-Feb-2007 :00:00:00', 'dd-mon-yyyy :hh24:mi:ss')
               + (ROWNUM - 1) / 24
                   hr
          FROM all_objects  -- use any big table, something with at least as many rows as hours in your range
         WHERE TO_DATE('01-Feb-2007 :00:00:00', 'dd-mon-yyyy :hh24:mi:ss')
               + (ROWNUM - 1) / 24 <=
                   TO_DATE(
                       '28-Feb-2007 :23:00:00',
                       'dd-mon-yyyy :hh24:mi:ss'
                   ))
 WHERE hr NOT IN
           (SELECT TRUNC(
                       TO_DATE(time_interval, 'dd/mm/yyyy hh24:mi:ss'),
                       'hh'
                   )
              FROM your_table);

Open in new window

0
 
HainKurtSr. System AnalystCommented:
I suggest create a table say N(d int), put 0,1,2,3,4,5,6,7,8,9 inside this table

create N4 as
select n1.d*1000 + n2.d*100 + n3.d*10 + n4.d as d from n n1, n n2, n n3, n n4

so when you say

select * from n4 where d<=366 : 0 .. 366
select * from n4 where d<=24 : 0 .. 24

we can use these in your queries

now

select date1 + DD.d + HH.h as datetime1,  date1 + DD.d + (HH.h+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=24) as HH

will give you all hour intervals for given dates : date1 & date2

say above query is Q

now we can join Q & yourTable

select datetime1, datetime2, (select count(1) from yourtable t where t.interval in datetime1 and datetime2) as r
from (Q)
order by datetime1

this query will give you all intervals & counts: you are asking for records where r = 0

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
HainKurtSr. System AnalystCommented:
fix to above: N4 is a view as

create view N4 as
select n1.d*1000 + n2.d*100 + n3.d*10 + n4.d as d from n n1, n n2, n n3, n n4
0
 
HainKurtSr. System AnalystCommented:
one more fix ;)

select date1 + DD.d + HH.h as datetime1,  date1 + DD.d + (HH.h+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=24) as HH

-->

select date1 + DD.d + (HH.h/24) as datetime1,  date1 + DD.d + (HH.h+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=24) as HH
0
 
HainKurtSr. System AnalystCommented:
I hate this, one more fix

select date1 + DD.d + (HH.h/24) as datetime1,  date1 + DD.d + (HH.h+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=24) as HH

-->

select date1 + DD.d + (HH.d/24) as datetime1,  date1 + DD.d + (HH.d+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=23) as HH
0
 
sdstuberCommented:
if you're only going to allow 9999 rows, why not simply use a table that already has that many.
yes, you could extend your view by creating as many joins as needed but a simple one column table of a million nulls  only takes up about 13Mb and can generate the rownum list easy enough.

Of course in versions 9i and above we'd use other methods like

(select level n from dual connect by level <= 1000000)

those and a simply NOT IN should be MUCH more efficient than all those joins and counting.
it will work, but a lot of hoops to jump through
0
 
HainKurtSr. System AnalystCommented:
I am using 9.2 and

select level n from dual connect by level <= 1000000

is giving me only one record...

N
1

0
 
sdstuberCommented:
you are encountering a bug,  are you by chance running 9.2.0.2?
0
 
sdstuberCommented:
one of the workarounds to the bug is to nest it in another inline view

select n from (select level n from dual connect by level <= 1000000)

0
 
HainKurtSr. System AnalystCommented:
Oracle9i Enterprise Edition 9.2.0.8
0
 
sdstuberCommented:
I've never had a problem with the connect by query in anything from 9.2.0.4 through 11.2.0.1

Either you are only fetching 1 row from the cursor, or you have encountered a bug.
It should work.   I recommend contacting oracle support.

However, this question is for 8i (at least according to the selected zone) and it definitely isn't supported there, hence the recommendation to use a large table.
0
 
nrajasekhar7Author Commented:
Thanks a lott...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.