Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Posts
    14

    Unanswered: May be there is a name for such queries but I don't know!

    hi,
    I have a table on ORACLE 10.1.0.3 where I kept the start and end time for specific programs.
    Lets say prog_history is the name of the table and the columns are

    name : VARCHAR
    starttime : DATE
    endtime : DATE

    There are overlapped programs such as:
    A|05.06.2005 00:05:02|05.06.2005 00:06:06
    B|05.06.2005 00:05:08|05.06.2005 00:13:14
    C|05.06.2005 00:05:14|05.06.2005 00:05:15
    D|05.06.2005 00:25:58|05.06.2005 00:33:43
    E|05.06.2005 00:26:00|05.06.2005 00:26:50
    F|05.06.2005 00:26:01|05.06.2005 01:13:41
    F|05.06.2005 01:50:01|05.06.2005 01:55:41


    And I have to find the time periods when there are NO programs running.

    For the example above ; the result is:
    00:13:14 to 00:25:58 and
    01:13:41 to 01:50:01

    Is there a method to get this data via SQL?
    I have data for 6 months and there are 54429 rows.

    I thought that creating a new table which has all seconds from June 2005 (as all_seconds as name) and then joining these tables on the start and end times with between operator will create a set of seconds where at least one program runs.Then getting the difference between all_seconds and the result set of the query will give me the all seconds when there are no programs running.Then I have to define the continious seconds in order to get the result.
    As you may guess , I'm just stucted just at the first point.

    There has to be a simple way for this kind of problems.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Your approach would work, but I think I have a more simple implementation.
    Initialize a column in the ALL_SECONDS table to a value; say 0.
    Read your other table & use the values to to UPDATE the column to 1 where the seconds match.
    After reading & processing every record in the start/end table, the rows in the ALL_SECONDS table which still contain 0's will be the periods where not activity occurred.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2004
    Posts
    60
    Is your table is having any primary key ? If yes what is formate of that.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try this
    Code:
    create table x
    (
    id	number,
    st  date, -- startttime
    et  date  -- endtime
    )
    
    select id, et, 'end' 
    from x x1
    where not exists(select 1 from x x2 where x1.et between x2.st and x2.et and x2.rowid != x1.rowid)
    union
    select id, st, 'start' 
    from x x1
    where not exists(select 1 from x x2 where x1.st between x2.st and x2.et and x2.rowid != x1.rowid)
    order by 2
    
    result is
    
    ID	interval	                         
    1	05/06/2005 00:05:02	start
    2	05/06/2005 00:13:14	end
    4	05/06/2005 00:25:58	start
    6	05/06/2005 01:13:41	end
    6	05/06/2005 01:50:01	start
    6	05/06/2005 01:55:41	end
    Alan

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I almost always try to avoid overlapping querys, especially when dealing with time/date boundaries, but you can try this. (you will have to especify a especific date!)
    Code:
    SQL> alter session set nls_date_format = 'mm.dd.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> var thedate varchar2(10)
    SQL> exec :thedate := '05/06/2005'
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from prog_history order by 2, 3;
    
    NAME  STARTTIME           ENDTIME
    ----- ------------------- -------------------
    A     05.06.2005 00:05:02 05.06.2005 00:06:06
    B     05.06.2005 00:05:08 05.06.2005 00:13:14
    C     05.06.2005 00:05:14 05.06.2005 00:05:15
    D     05.06.2005 00:25:58 05.06.2005 00:33:43
    E     05.06.2005 00:26:00 05.06.2005 00:26:50
    F     05.06.2005 00:26:01 05.06.2005 01:13:41
    F     05.06.2005 01:50:01 05.06.2005 01:55:41
    
    7 rows selected.
    
    SQL> select max( endtime ), t
      2    from (
      3  select starttime,
      4         endtime,
      5         ( select min( starttime )
      6             from prog_history
      7            where starttime > t.endtime ) t
      8    from prog_history t
      9   where starttime between to_date( :thedate, 'mm/dd/yyyy' )
     10                       and to_date( :thedate, 'mm/dd/yyyy' )+1-1/24/60/60
     11   order by 2, 3
     12         )
     13   where t is not null
     14   group by t
     15  /
    
    MAX(ENDTIME)        T
    ------------------- -------------------
    05.06.2005 00:13:14 05.06.2005 00:25:58
    05.06.2005 01:13:41 05.06.2005 01:50:01
    
    SQL>
    Last edited by JMartinez; 02-03-06 at 15:38.

  6. #6
    Join Date
    Apr 2005
    Posts
    14
    Thanks to all of you,
    this forum is great.
    I solved someone's problem and my 2 problems are solved.
    Thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •