Hi I am looking for some help in regards to writing a query to tell me if a record exists the next day. I have the following data (example snapshot for 2 days worth) with 3 different scenarios.
The following are my create and insert queries:
CREATE TABLE WORK_RECORDS
( "REC_DATE" DATE,
"ORDER_NUMBER" VARCHAR2(255 BYTE),
"QUEUE" VARCHAR2(255 BYTE)
)
Insert:
Insert into WORK_RECORDS (REC_DATE,ORDER_NUMBER,QUEUE) values (to_date('01-NOV-18','DD-MON-RR'),'8209555', 'QUEUE A');
Insert into WORK_RECORDS (REC_DATE,ORDER_NUMBER,QUEUE) values (to_date('02-NOV-18','DD-MON-RR'),'8209555', 'QUEUE A');
Insert into WORK_RECORDS (REC_DATE,ORDER_NUMBER,QUEUE) values (to_date('02-NOV-18','DD-MON-RR'),'5059859', 'QUEUE A');
Insert into WORK_RECORDS (REC_DATE,ORDER_NUMBER,QUEUE) values (to_date('01-NOV-18','DD-MON-RR'),'7730451', 'QUEUE A');
From this data I need to identify the following scenarion:
1. If record exists for previous day then assign this as 'Existing'
2. If records does not exist for previous day then assign this as 'New'
3. If record exists for previous day but not a date after then assign this as 'Completed'
I have wrote the following query to give me a count of the records but I am not sure how to account for the dates. Also going forward more dates will be added but thought its better for me to work with a smaller period to try and get this to work before adding more values.
SELECT
ORDER_NUMBER,
COUNT(ORDER_NUMBER),
CASE
COUNT(ORDER_NUMBER)
WHEN 1 THEN 'NEW'
ELSE 'CURRENT'
END AS STATUS
FROM
WORK_RECORDS
Required Output:
The following is the output that I require:
REC_DATE, ORDER_NUMBER, STATUS
'02-NOV-18','8209555','EXISTING'
'02-NOV-18','5059859','NEW'
'01-NOV-18','7730451','COMPLETED'
The last record needs to be assigned completed for the '01 NOV 18' as no record exists for the next day.
Would really appreciate it if somebody could advise on how I can add the date elements to this query, also one thing to bear in mind is that once I add more data this will have data for atleast 3 months but thought its better I work out how to make this work on 2 days before thinking about the rest.