-----------------------------------------------------------
--        name notthere.sql
--     version 0.0
-- description Counts events per minute recorded in a table
--             'tt', including counts for minutes that are
--             not there. 
--    synopsis No arguments.
--       usage SQL> notthere
--    comments I thought this was a cute problem when it
--             described to me, so here is the solution 
--             I found for it. The need is to count all the
--             events per minute recorded in a table of 
--             events. (Ultimately the events come from a 
--             real-time communication system.) In order to
--             report these sensibly, it's 
--             necessary to print out a zero when there 
--             is no row in the table of events for a 
--             particular minute. So how do you count a 
--             non-existent row? 
--    creation 03 Nov 2006
--   copyright David Clement (2006). Use it if you like it.
--    warranty None expressed or implied.
--      author David Clement
--     changes
----------------------------------------------------------
set serveroutput on size 100000

create type typ_minutes as table of date
/

declare
    tbl_minutes typ_minutes := typ_minutes ( );

    -- these are the control knobs 
    n_lim number := 4;
    d_begin date := to_date ('03-NOV-2006 08:53:00',
                    'DD-MON-YYYY HH24:MI:SS');
begin
    -- load an array of minutes
    tbl_minutes.extend (n_lim);
    for i in 1 .. n_lim
    loop
        tbl_minutes (i) := d_begin + (i - 1) / 1440;
    end loop;

    -- left outer join to what we are counting
    -- and then show the result
    for j in (select a.column_value as m, 
        count (tt.event) as c
        from table (cast (tbl_minutes as typ_minutes) ) a
        left outer join tt
        on trunc (a.column_value, 'mi')
            = trunc (tt.minute, 'mi')
        group by a.column_value
        order by 1)
    loop
        dbms_output.put_line (to_char (j.m, 'hh24:mi:ss')
        || ' '
        || to_char (j.c) );
    end loop;
end;
/
/* Here is a SQL-only solution to a similar problem,
with a rownum gimmick suggested by my colleague Hank Geerling. 
We get all the unit numbers that are -not-
assigned to a function group, where the unit numbers
are in the range 1 - 256. 
16-Aug-2007.
*/ 
SELECT MIN (foo.rn), MIN (f.fx_id), MIN (g.grp_#)
  FROM (SELECT ROWNUM AS rn
          FROM some_other_table
         WHERE ROWNUM < 257) foo,
       function_group f,
       group_asgt g
 WHERE f.fx_id = g.fx_id
   AND f.grp_# = g.grp_#
   AND f.profile_id = 49
   AND f.path_id = 2441
   AND f.cycle_id = 1
   AND NOT EXISTS (
          SELECT 1
            FROM function_group f, group_asgt g
           WHERE f.fx_id = g.fx_id
             AND f.grp_# = g.grp_#
             AND f.profile_id = 49
             AND f.path_id = 2441
             AND f.cycle_id = 1
             AND g.unit_# = foo.rn);