drop table LAB_SAMPLES
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
drop sequence LAB_SAMPLES_SEQ
ORA-02289: sequence does not existMore Details: https://docs.oracle.com/error-help/db/ora-02289
create sequence LAB_SAMPLES_SEQ
Sequence created.
create table LAB_SAMPLES
( sample_id int default LAB_SAMPLES_SEQ.NEXTVAL,
date_taken date
)
Table created.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-02')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-03')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-04')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-07')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-08')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-09')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-10')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-14')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-15')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-16')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-19')
1 row(s) inserted.
insert into LAB_SAMPLES ( date_taken) values (date '2015-12-20')
1 row(s) inserted.
select * from LAB_SAMPLES order by 2
SAMPLE_ID | DATE_TAKEN | 1 | 01-DEC-15 | 2 | 02-DEC-15 | 3 | 03-DEC-15 | 4 | 04-DEC-15 | 5 | 07-DEC-15 | 6 | 08-DEC-15 | 7 | 09-DEC-15 | 8 | 10-DEC-15 | 9 | 14-DEC-15 | 10 | 15-DEC-15 | 11 | 16-DEC-15 | 12 | 19-DEC-15 | 13 | 20-DEC-15 |
---|
select
date_taken,
lag(date_taken) over ( order by date_taken) prev
from lab_samples
order by 1
DATE_TAKEN | PREV | 01-DEC-15 | - | 02-DEC-15 | 01-DEC-15 | 03-DEC-15 | 02-DEC-15 | 04-DEC-15 | 03-DEC-15 | 07-DEC-15 | 04-DEC-15 | 08-DEC-15 | 07-DEC-15 | 09-DEC-15 | 08-DEC-15 | 10-DEC-15 | 09-DEC-15 | 14-DEC-15 | 10-DEC-15 | 15-DEC-15 | 14-DEC-15 | 16-DEC-15 | 15-DEC-15 | 19-DEC-15 | 16-DEC-15 | 20-DEC-15 | 19-DEC-15 |
---|
select
date_taken,
case
when nvl(lag(date_taken) over (order by date_taken),date_taken) != date_taken-1
then date_taken end loval
from lab_samples
order by 1
DATE_TAKEN | LOVAL | 01-DEC-15 | 01-DEC-15 | 02-DEC-15 | - | 03-DEC-15 | - | 04-DEC-15 | - | 07-DEC-15 | 07-DEC-15 | 08-DEC-15 | - | 09-DEC-15 | - | 10-DEC-15 | - | 14-DEC-15 | 14-DEC-15 | 15-DEC-15 | - | 16-DEC-15 | - | 19-DEC-15 | 19-DEC-15 | 20-DEC-15 | - |
---|
select date_taken, max(loval) over (order by date_taken) loval
from (
select date_taken,
case
when nvl(lag(date_taken) over (order by date_taken),date_taken) !=
date_taken-1 then date_taken end loval
from lab_samples )
order by 1
DATE_TAKEN | LOVAL | 01-DEC-15 | 01-DEC-15 | 02-DEC-15 | 01-DEC-15 | 03-DEC-15 | 01-DEC-15 | 04-DEC-15 | 01-DEC-15 | 07-DEC-15 | 07-DEC-15 | 08-DEC-15 | 07-DEC-15 | 09-DEC-15 | 07-DEC-15 | 10-DEC-15 | 07-DEC-15 | 14-DEC-15 | 14-DEC-15 | 15-DEC-15 | 14-DEC-15 | 16-DEC-15 | 14-DEC-15 | 19-DEC-15 | 19-DEC-15 | 20-DEC-15 | 19-DEC-15 |
---|
select min(date_taken) range_start, max(date_taken) range_end
from (
select date_taken,max(loval) over (order by date_taken) loval
from (
select date_taken,
case
when nvl(lag(date_taken) over (order by date_taken),date_taken) !=
date_taken-1 then date_taken end loval
from lab_samples))
group by loval
order by 1
RANGE_START | RANGE_END | 01-DEC-15 | 04-DEC-15 | 07-DEC-15 | 10-DEC-15 | 14-DEC-15 | 16-DEC-15 | 19-DEC-15 | 20-DEC-15 |
---|