question

The requirement is to consider just that minute for those orders which were created and completed within the same minute, and to start from the next minute otherwise. The orders are filtered to only include the orders created during the same day in question. I assume that such day is today (current day), if this needs to be changed to, say, yesterday then please modify the select part of the minutes CTE and where clause of orders CTE. The reason I include the orders CTE is to simply avoid an awkward-looking join condition. The orders CTE selects the order start date (with seconds discarded) for those orders which get completed within the same minute, or the order start date (still with seconds discarded) plus one minute for all other orders. The orders' completed date is the completed date with seconds discarded (this is not technically necessary to discard the seconds from completed date, but is done for consistency). Here is the script (please note that the join condition is now simple with timestamp data type on both ends): with minutes (minute) as ( select cast(trunc(sysdate) as timestamp) + interval '1' minute * (level - 1) from dual connect by level <= 1440 ), orders (orderid, odcreate, odcomplete) as ( select orderid, cast( case when to_char(odcreate, 'hh24:mi') = to_char(odcomplete, 'hh24:mi') then trunc(odcreate, 'mi') else trunc(odcreate, 'mi') + interval '1' minute end as timestamp ) odcreate, cast(trunc(odcomplete, 'mi') as timestamp) odcomplete from ord_order where odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day ) select to_char(m.minute, 'hh24:mi') minute, count(o.orderid) orders_count from minutes m left join orders o on m.minute between o.odcreate and o.odcomplete group by m.minute order by m.minute; Based on the sample data in question, the script produces the following result (abridged): Minute Orders_Count ------ ------------ 00:00 0 00:01 0 --- --- 00:12 2 --- --- 01:13 1 01:14 1 01:15 0 --- --- 23:59 0 I found out that [SQL Fiddle][1] provides a pretty robust online IDE for debugging Oracle queries, as well as many others (Postgre, SQLite, MySQL), so I used this site to test the script. **P.S.** There is a comment about possible modifications to the script in order to attempt to improve performance. For some reason the script gets chopped of in the comments (it never happened before, just today), so here is this script: with minutes (minute) as ( select cast(trunc(sysdate) as timestamp) + interval '1' minute * (level - 1) from dual connect by level <= 1440 ) select to_char(m.minute, 'hh24:mi') minute, count(o.orderid) orders_count from minutes m left join ord_order o on m.minute between case when to_char(o.odcreate, 'hh24:mi') = to_char(o.odcomplete, 'hh24:mi') then trunc(o.odcreate, 'mi') else trunc(o.odcreate, 'mi') + interval '1' minute end and o.odcomplete and ( odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day ) group by m.minute order by m.minute; This version makes the join condition somewhat awkward, but it reduces the number of CTE used from 2 to 1, replacing the second CTE with the actual table. Hopefully, this will make the query faster. ***Edit to remove connect by and to move the column alias inside the CTE definition per *** @Usman Butt ***comment. Otherwise, the script breaks in 10g*** Because the query as written appears to error out in 10g, you can try a slightly different approach (same logic). There are 2 possibly questionable parts: **connect by** inside of the CTE definition and the column aliasing inside of the second CTE. The latter is not an issue if the second query is used (which used but only one CTE, not 2). In order to substitute the inherently recursive **connect by**, some arbitrary table is needed. Any table/view will do so long as it has 1440 or more rows. In the scripts below, I opted to use **all\_objects** but again, any table/view with enough rows should do the trick. There are 2 scripts below, they are very similar. The only difference is that the first one uses the timestamp data type while the second - varchar2(5). This should not make any difference in performance but please try both and retain the one you like. Here is the first script: -- method A, using the timestamp with minutes as ( select cast(trunc(sysdate) as timestamp) + interval '1' minute * (rownum - 1) as minute from all_objects where rownum <= 1440 ) select to_char(m.minute, 'hh24:mi') minute, count(o.orderid) orders_count from minutes m left join ord_order o on m.minute between case when to_char(o.odcreate, 'hh24:mi') = to_char(o.odcomplete, 'hh24:mi') then trunc(o.odcreate, 'mi') else trunc(o.odcreate, 'mi') + interval '1' minute end and o.odcomplete and ( odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day ) group by m.minute order by m.minute; Here is the second script: -- method B, using just the time part formatted as hh24:mi (varchar2(5) type) with minutes as ( select lpad(floor((rownum - 1)/60), 2, '0')||':'||lpad(mod((rownum - 1), 60), 2, '0') as minute from all_objects where rownum <= 1440 ) select m.minute, count(o.orderid) orders_count from minutes m left join ord_order o on m.minute between case when to_char(o.odcreate, 'hh24:mi') = to_char(o.odcomplete, 'hh24:mi') then to_char(o.odcreate, 'hh24:mi') else to_char(o.odcreate + interval '1' minute, 'hh24:mi') end and to_char(o.odcomplete, 'hh24:mi') and ( odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day ) group by m.minute order by m.minute; Both produce the same result and should run just fine in 10g. Hope this helps. Oleg [1]: http://sqlfiddle.com/#!4