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

From: Nikolas Tautenhahn <virtual_at_gmx.de>
Date: Sat, 28 Jul 2007 15:51:47 +0200
Message-ID: <f8fhlj$4il$03$1@news.t-online.com>

Hi,

Michel Cadot wrote:
>
> SQL> with cal as
> 2 ( select to_date('&start','DD/MM/YYYY HH24:MI')+(rownum-1)/1440 tim
> 3 from dual
> 4 connect by level <= &duration )
> 5 select cal.tim, sum(decode(t.c_date,null,0,1)) "nbIn"
> 6 from cal, t
> 7 where t.c_date (+) < cal.tim and (t.c_date(+)+duration(+)/86400) >= cal.tim
> 8 group by cal.tim
> 9 order by cal.tim
> 10 /

That was what I had in mind... it is quite slow on real data, but the trick with "connect by level" was a great thing to learn - thanks a lot for that!

Now if I could just make it a bit more efficient... Maybe it would be better to cycle over the data and "create" the minutes which are between start and end instead of creating the minutes first and searching for matching data afterwards (maybe grouping after the minutes)...

Ah well it is not easy, but at least there is one working example :)

Regards,
N. Received on Sat Jul 28 2007 - 08:51:47 CDT