Home » SQL & PL/SQL » SQL & PL/SQL » dividing time when two hour gap found (oracle 10g, 10.0.0.2 , windows XP)
dividing time when two hour gap found [message #283496] Tue, 27 November 2007 05:55 Go to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
hi all,

my table creation script is


create table R
(
  TIM DATE,
  OH  NUMBER
)


and insert into values are



insert into r values('27/NOV/2007 16:01:00');
insert into r values('27/NOV/2007 16:11:00');
insert into r values('27/NOV/2007 16:21:00');
insert into r values('27/NOV/2007 19:01:00');
insert into r values('27/NOV/2007 19:11:00');
insert into r values('27/NOV/2007 20:01:00');
insert into r values('27/NOV/2007 20:11:00');


I need the output like this
in which whenever there is gap of two hour or greater
then the next date min,max should be according to that

output like

MINT                    MAXT
11/27/2007 4:01:00 PM	11/27/2007 4:21:00 PM
11/27/2007 7:01:00 PM   11/27/2007 8:11:00 PM


so when there comes the gap of two hours
then the min and max again calculate
starting from the break position



thanks in advance

Re: dividing time when two hour gap found [message #283506 is a reply to message #283496] Tue, 27 November 2007 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ table contains 2 columns, insert 1 value -> syntax error
2/ '27/NOV/2007 16:01:00' is not a date, it is a string -> value error
3/
Quote:

when there comes the gap of two hours

Gap between what and what? the previous and the current value? or the current value and the first value of the previous group?

Regards
Michel
Re: dividing time when two hour gap found [message #283510 is a reply to message #283496] Tue, 27 November 2007 06:30 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

which oracle version your using ???
oracle 10g, 10.0.0.2 is it beta version ?
Re: dividing time when two hour gap found [message #283724 is a reply to message #283496] Wed, 28 November 2007 00:58 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
it's the gap between the last value of previous grouping
and the first value of next grouping

as u can see there comes the gap of two hours
after
11/27/2007 4:21:00 PM


and then comes the next value
11/27/2007 7:01:00 PM


as I got the diffrence to be two hours then
the grouping must look like this one

MINT                    MAXT
11/27/2007 4:01:00 PM	11/27/2007 4:21:00 PM
11/27/2007 7:01:00 PM   11/27/2007 8:11:00 PM
Re: dividing time when two hour gap found [message #283733 is a reply to message #283496] Wed, 28 November 2007 01:06 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Michel Cadot
Quote:

1/ table contains 2 columns, insert 1 value -> syntax error
2/ '27/NOV/2007 16:01:00' is not a date, it is a string -> value error


Proper insert statements required.

Regards,
Kiran.
Re: dividing time when two hour gap found [message #283740 is a reply to message #283724] Wed, 28 November 2007 01:23 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table R (TIM DATE);
SQL> alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS' nls_date_language=american;
SQL> insert into r values('27/NOV/2007 16:01:00');
SQL> insert into r values('27/NOV/2007 16:11:00');
SQL> insert into r values('27/NOV/2007 16:21:00');
SQL> insert into r values('27/NOV/2007 19:01:00');
SQL> insert into r values('27/NOV/2007 19:11:00');
SQL> insert into r values('27/NOV/2007 20:01:00');
SQL> insert into r values('27/NOV/2007 20:11:00');
SQL> commit;
SQL> select * from r order by tim;
TIM
--------------------
27/NOV/2007 16:01:00
27/NOV/2007 16:11:00
27/NOV/2007 16:21:00
27/NOV/2007 19:01:00
27/NOV/2007 19:11:00
27/NOV/2007 20:01:00
27/NOV/2007 20:11:00

7 rows selected.

SQL> with 
  2    step1 as (
  3      select tim,
  4             case 
  5               when nvl((tim-lag(tim) over (order by tim))*24,3) > 2
  6                 then row_number() over (order by tim) 
  7             end grp
  8      from r
  9    ),
 10    step2 as (
 11      select tim, max(grp) over (order by tim) grp
 12      from step1
 13    )
 14  select min(tim) mint, max(tim) maxt
 15  from step2
 16  group by grp
 17  order by grp
 18  /
MINT                 MAXT
-------------------- --------------------
27/NOV/2007 16:01:00 27/NOV/2007 16:21:00
27/NOV/2007 19:01:00 27/NOV/2007 20:11:00

2 rows selected.

Regards
Michel
Previous Topic: Ora-0604
Next Topic: PROBLEM WHILE CRETAING AN INDEX WITH PARTITIONS FOR AN EXISTING TABLE
Goto Forum:
  


Current Time: Tue Dec 06 08:43:07 CST 2016

Total time taken to generate the page: 0.06441 seconds