0

Currently I am having trouble trying to query a data set that contains multiple records for unique customers. Each row has a start date and end date and the most recent record marked as Most Current and end date as 12/31/9999. I am trying to write a query so that I can return all records that fall on a specific date such as months end (10/31/2018). My goal in Oracle SQL Developer is to type in all records/data on the day 10/31/2017 so the rows with the * are the only ones Id want back. The final outcome is to do this with a much larger file and see the entire year at months end where each person was at based on the month end date for 1 year.

Example Data

Pythonnoob12
  • 3
  • 4
  • It was unclear what your requirements were because your sample data does not include examples that cover the edge cases. Technically, Brekhnaa's answer is correct because it gives the results you marked as desired. My answer was correct because it happens to match your intended meaning. You should have included at least one example record where the End date was not 12/31/9999 but that was intended to be included in the result set. – Jeffrey Kemp Dec 28 '18 at 06:12

2 Answers2

0

Can you elaborate what output you are expecting? What i can see is that you are trying to implement temporal functionality here. Kindly check this link if this serves your purpose Oracle temporal . As for highlighted records, you can use a simple condition in where clause like this and format date part as required:

select * from emp where  endd=to_date('9999/12/31','yyyy/mm/dd')
Brekhnaa
  • 36
  • 4
0

I think you want to list any records that are current as of a specified date. To do this I'd simply use a BETWEEN condition:

select * from mytable
where date'2017-31-10' between start and end
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158