Morning All,
I have a query which seems to be straightforward but having issues trying to get the data needed. I am using oracle version 12c. The sql will be put into a package but firstly wanted to get it working in a sql session.. Details as below in a simplified form
create table test
(
LOCATION VARCHAR2( 5 BYTE),
START_DATE DATE,
END_DATE DATE
);
INSERT INTO TEST
VALUES('TAB18','13-FEB-2019','15-APR-2019');
INSERT INTO TEST
VALUES('DHA18','15-APR-2019', '11-SEP-2019');
INSERT INTO TEST
VALUES('KHA18','11-SEP-2019',NULL);
COMMIT;
I wish to bring back the location whereby if a particular date is within a date range then bring the location back for that. eg if date was '01-mar-2019' then the location brought back would be 'TAB18' . However if the date is '11-SEP-2019' then I only need to bring back 'KHA18' as that is still active ie has no end date, rather than DHA18 and KHA18 because it falls within two date ranges.
select location from test
where (to_date('11-SEP-2019','DD-MON-RRRR') BETWEEN START_DATE AND END_DATE
OR END_DATE IS NULL);
I believe 'coalesce' could be used possibly in this instance but not sure how.
Thanks in advance
Regards