drop table ORDERS purge
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table ORDERS
( order_id int,
status_date date,
status varchar2(20)
)
Table created.
insert into ORDERS values (11700, date '2016-01-03', 'New')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-04', 'Inventory Check')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-05', 'Inventory Check')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-06', 'Inventory Check')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-07', 'Inventory Check')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-08', 'Inventory Check')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-09', 'Awaiting Signoff')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-10', 'Awaiting Signoff')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-11', 'Awaiting Signoff')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-12', 'In Warehouse')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-13', 'In Warehouse')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-14', 'In Warehouse')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-15', 'Awaiting Signoff')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-16', 'Awaiting Signoff')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-17', 'Payment Pending')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-18', 'Payment Pending')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-19', 'Awaiting Signoff')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-20', 'Awaiting Signoff')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-21', 'Delivery')
1 row(s) inserted.
insert into ORDERS values (11700, date '2016-01-22', 'Delivery')
1 row(s) inserted.
commit
Statement processed.
select * from orders order by 1,2
ORDER_ID | STATUS_DATE | STATUS | 11700 | 03-JAN-16 | New | 11700 | 04-JAN-16 | Inventory Check | 11700 | 05-JAN-16 | Inventory Check | 11700 | 06-JAN-16 | Inventory Check | 11700 | 07-JAN-16 | Inventory Check | 11700 | 08-JAN-16 | Inventory Check | 11700 | 09-JAN-16 | Awaiting Signoff | 11700 | 10-JAN-16 | Awaiting Signoff | 11700 | 11-JAN-16 | Awaiting Signoff | 11700 | 12-JAN-16 | In Warehouse | 11700 | 13-JAN-16 | In Warehouse | 11700 | 14-JAN-16 | In Warehouse | 11700 | 15-JAN-16 | Awaiting Signoff | 11700 | 16-JAN-16 | Awaiting Signoff | 11700 | 17-JAN-16 | Payment Pending | 11700 | 18-JAN-16 | Payment Pending | 11700 | 19-JAN-16 | Awaiting Signoff | 11700 | 20-JAN-16 | Awaiting Signoff | 11700 | 21-JAN-16 | Delivery | 11700 | 22-JAN-16 | Delivery |
---|
select status, min(status_date) from_date, max(status_date) to_date
from orders
group by status
order by 2
STATUS | FROM_DATE | TO_DATE | New | 03-JAN-16 | 03-JAN-16 | Inventory Check | 04-JAN-16 | 08-JAN-16 | Awaiting Signoff | 09-JAN-16 | 20-JAN-16 | In Warehouse | 12-JAN-16 | 14-JAN-16 | Payment Pending | 17-JAN-16 | 18-JAN-16 | Delivery | 21-JAN-16 | 22-JAN-16 |
---|
select
order_id,
status_date,
status,
lag(status,1) over
(partition by order_id order by status_date) lag_status
from ORDERS
order by 1,2
ORDER_ID | STATUS_DATE | STATUS | LAG_STATUS | 11700 | 03-JAN-16 | New | - | 11700 | 04-JAN-16 | Inventory Check | New | 11700 | 05-JAN-16 | Inventory Check | Inventory Check | 11700 | 06-JAN-16 | Inventory Check | Inventory Check | 11700 | 07-JAN-16 | Inventory Check | Inventory Check | 11700 | 08-JAN-16 | Inventory Check | Inventory Check | 11700 | 09-JAN-16 | Awaiting Signoff | Inventory Check | 11700 | 10-JAN-16 | Awaiting Signoff | Awaiting Signoff | 11700 | 11-JAN-16 | Awaiting Signoff | Awaiting Signoff | 11700 | 12-JAN-16 | In Warehouse | Awaiting Signoff | 11700 | 13-JAN-16 | In Warehouse | In Warehouse | 11700 | 14-JAN-16 | In Warehouse | In Warehouse | 11700 | 15-JAN-16 | Awaiting Signoff | In Warehouse | 11700 | 16-JAN-16 | Awaiting Signoff | Awaiting Signoff | 11700 | 17-JAN-16 | Payment Pending | Awaiting Signoff | 11700 | 18-JAN-16 | Payment Pending | Payment Pending | 11700 | 19-JAN-16 | Awaiting Signoff | Payment Pending | 11700 | 20-JAN-16 | Awaiting Signoff | Awaiting Signoff | 11700 | 21-JAN-16 | Delivery | Awaiting Signoff | 11700 | 22-JAN-16 | Delivery | Delivery |
---|
select
order_id,
status_date,
status,
lag(status,1) over (partition by order_id order by status_date) lag_status,
lead(status,1) over (partition by order_id order by status_date) lead_status
from ORDERS
order by 1,2
ORDER_ID | STATUS_DATE | STATUS | LAG_STATUS | LEAD_STATUS | 11700 | 03-JAN-16 | New | - | Inventory Check | 11700 | 04-JAN-16 | Inventory Check | New | Inventory Check | 11700 | 05-JAN-16 | Inventory Check | Inventory Check | Inventory Check | 11700 | 06-JAN-16 | Inventory Check | Inventory Check | Inventory Check | 11700 | 07-JAN-16 | Inventory Check | Inventory Check | Inventory Check | 11700 | 08-JAN-16 | Inventory Check | Inventory Check | Awaiting Signoff | 11700 | 09-JAN-16 | Awaiting Signoff | Inventory Check | Awaiting Signoff | 11700 | 10-JAN-16 | Awaiting Signoff | Awaiting Signoff | Awaiting Signoff | 11700 | 11-JAN-16 | Awaiting Signoff | Awaiting Signoff | In Warehouse | 11700 | 12-JAN-16 | In Warehouse | Awaiting Signoff | In Warehouse | 11700 | 13-JAN-16 | In Warehouse | In Warehouse | In Warehouse | 11700 | 14-JAN-16 | In Warehouse | In Warehouse | Awaiting Signoff | 11700 | 15-JAN-16 | Awaiting Signoff | In Warehouse | Awaiting Signoff | 11700 | 16-JAN-16 | Awaiting Signoff | Awaiting Signoff | Payment Pending | 11700 | 17-JAN-16 | Payment Pending | Awaiting Signoff | Payment Pending | 11700 | 18-JAN-16 | Payment Pending | Payment Pending | Awaiting Signoff | 11700 | 19-JAN-16 | Awaiting Signoff | Payment Pending | Awaiting Signoff | 11700 | 20-JAN-16 | Awaiting Signoff | Awaiting Signoff | Delivery | 11700 | 21-JAN-16 | Delivery | Awaiting Signoff | Delivery | 11700 | 22-JAN-16 | Delivery | Delivery | - |
---|
select order_id,
status_date,
status,
lag(status) over (partition by order_id order by status_date) lag_status,
lead(status) over (partition by order_id order by status_date) lead_status,
lag(status_date) over (partition by order_id order by status_date) lag_status_date,
lead(status_date) over (partition by order_id order by status_date) lead_status_date
from ORDERS
order by 1,2
ORDER_ID | STATUS_DATE | STATUS | LAG_STATUS | LEAD_STATUS | LAG_STATUS_DATE | LEAD_STATUS_DATE | 11700 | 03-JAN-16 | New | - | Inventory Check | - | 04-JAN-16 | 11700 | 04-JAN-16 | Inventory Check | New | Inventory Check | 03-JAN-16 | 05-JAN-16 | 11700 | 05-JAN-16 | Inventory Check | Inventory Check | Inventory Check | 04-JAN-16 | 06-JAN-16 | 11700 | 06-JAN-16 | Inventory Check | Inventory Check | Inventory Check | 05-JAN-16 | 07-JAN-16 | 11700 | 07-JAN-16 | Inventory Check | Inventory Check | Inventory Check | 06-JAN-16 | 08-JAN-16 | 11700 | 08-JAN-16 | Inventory Check | Inventory Check | Awaiting Signoff | 07-JAN-16 | 09-JAN-16 | 11700 | 09-JAN-16 | Awaiting Signoff | Inventory Check | Awaiting Signoff | 08-JAN-16 | 10-JAN-16 | 11700 | 10-JAN-16 | Awaiting Signoff | Awaiting Signoff | Awaiting Signoff | 09-JAN-16 | 11-JAN-16 | 11700 | 11-JAN-16 | Awaiting Signoff | Awaiting Signoff | In Warehouse | 10-JAN-16 | 12-JAN-16 | 11700 | 12-JAN-16 | In Warehouse | Awaiting Signoff | In Warehouse | 11-JAN-16 | 13-JAN-16 | 11700 | 13-JAN-16 | In Warehouse | In Warehouse | In Warehouse | 12-JAN-16 | 14-JAN-16 | 11700 | 14-JAN-16 | In Warehouse | In Warehouse | Awaiting Signoff | 13-JAN-16 | 15-JAN-16 | 11700 | 15-JAN-16 | Awaiting Signoff | In Warehouse | Awaiting Signoff | 14-JAN-16 | 16-JAN-16 | 11700 | 16-JAN-16 | Awaiting Signoff | Awaiting Signoff | Payment Pending | 15-JAN-16 | 17-JAN-16 | 11700 | 17-JAN-16 | Payment Pending | Awaiting Signoff | Payment Pending | 16-JAN-16 | 18-JAN-16 | 11700 | 18-JAN-16 | Payment Pending | Payment Pending | Awaiting Signoff | 17-JAN-16 | 19-JAN-16 | 11700 | 19-JAN-16 | Awaiting Signoff | Payment Pending | Awaiting Signoff | 18-JAN-16 | 20-JAN-16 | 11700 | 20-JAN-16 | Awaiting Signoff | Awaiting Signoff | Delivery | 19-JAN-16 | 21-JAN-16 | 11700 | 21-JAN-16 | Delivery | Awaiting Signoff | Delivery | 20-JAN-16 | 22-JAN-16 | 11700 | 22-JAN-16 | Delivery | Delivery | - | 21-JAN-16 | - |
---|
select
order_id,
status,
lag(status_date) over (partition by order_id order by status_date) from_date,
status_date to_date
from (
select
order_id,
status_date,
status,
lag(status) over (partition by order_id order by status_date) lag_status,
lead(status) over (partition by order_id order by status_date) lead_status
from ORDERS
)
where lag_status is null
or lead_status is null
or lead_status <> status
order by 1,3 nulls first
ORDER_ID | STATUS | FROM_DATE | TO_DATE | 11700 | New | - | 03-JAN-16 | 11700 | Inventory Check | 03-JAN-16 | 08-JAN-16 | 11700 | Awaiting Signoff | 08-JAN-16 | 11-JAN-16 | 11700 | In Warehouse | 11-JAN-16 | 14-JAN-16 | 11700 | Awaiting Signoff | 14-JAN-16 | 16-JAN-16 | 11700 | Payment Pending | 16-JAN-16 | 18-JAN-16 | 11700 | Awaiting Signoff | 18-JAN-16 | 20-JAN-16 | 11700 | Delivery | 20-JAN-16 | 22-JAN-16 |
---|