Re: query: "count" each minute between start/end

Hi,

Michel Cadot wrote:
"Nikolas Tautenhahn" <virtual@xxxxxx> a écrit dans le message de news: f8fhlj$4il$03$1@xxxxxxxxxxxxxxxxxxxx
It depends if you want each and every minute or only the minutes when
there is someone.

It would be sufficient to get only the minutes when there is someone. I got this by removing an outer join:

with cal as
( select sysdate - (1/12) +(rownum-1)/1440 tim
from dual
connect by level <= 120 )
select cal.tim, sum(decode(testlog.testlog_c_date,null,0,1)) "nbIn"
from cal, testlog
where testlog.testlog_c_date >= sysdate - (1/12) and testlog.testlog_c_date < cal.tim and (testlog.testlog_c_date(+)+testlog.testlog_x_duration(+)/86400) >= cal.tim
group by cal.tim
order by cal.tim

this was done with real data and was fast for the example above (going 2hrs into history took 1.6 seconds, 6hrs took 20 seconds and going back 18hrs took 180s... well and this calculation would be needed for the last 21days with approx. 5000-8000 datasets per day.

Unfortunately I don't have a sqlplus at the moment but only the sqldeveloper. Its output of explain plan was exported to html and the source code uploaded to:

http://np.cryosphere.de/p:f02423f7c5f0ce859a727395673715c0/plain

All other exports were less readable, sorry for the mess...

Regards,
N.
.

Relevant Pages

  • Re: query: "count" each minute between start/end
    ... | Unfortunately I don't have a sqlplus at the moment but only the ... | All other exports were less readable, ... Michel Cadot ...
    (comp.databases.oracle.misc)
  • Re: Export of Data Unstable
    ... > I have an Integrated Plan linked to approx 20 sub-projects. ... > all of the data out to Excel, I have recently noticed that not all of the ... > then open it directly and repeat the export it exports everything as required. ...
    (microsoft.public.project)
  • Export of Data Unstable
    ... I have an Integrated Plan linked to approx 20 sub-projects. ... all of the data out to Excel, I have recently noticed that not all of the ... then open it directly and repeat the export it exports everything as required. ...
    (microsoft.public.project)