Get active rows based on date fields-CodePudding

ID JOB_ID   RATE      START_DT     END_DT
43  41       11      06/06/2022 
42  42       12      06/06/2022 
41  43       10      06/06/2022   06/17/2022
62  43       15      06/17/2022   06/21/2022
63  43       18.5    06/21/2022   06/22/2022
65  43       19.5    06/22/2022 
44  45       15      06/06/2022 
45  46       16      06/06/2022 
46  47       19      06/06/2022 
47  48       20      06/06/2022 
48  49       25      06/06/2022

I am trying to get the active rates for every job. If a job has an end date that is null, then that just means that is the active rate for this job since there is no end date. Future end dates can be added as they are for JOB_ID = 43. For this job, you can see there are 4 rows with some future rates specified. If today is June 11th, then the row with ID = 41 should be returned as it is the currently active rate. To just get this row I would run a query where I check if the sysdate between start_dt and end_dt. This would only return this row, but I want to return in one query all the jobs and their current rate on the date the query is run. As you can see, job 43 also has rates in the future with no end date. I am unsure how to query this. I have tried different combinations of aggregation functions like max on the date fields, but I have not been successful.

The output I am trying to achieve is this:

    ID JOB_ID   RATE      START_DT     END_DT
    43  41       11      06/06/2022 
    42  42       12      06/06/2022 
    41  43       10      06/06/2022   06/17/2022
    44  45       15      06/06/2022 
    45  46       16      06/06/2022 
    46  47       19      06/06/2022 
    47  48       20      06/06/2022 
    48  49       25      06/06/2022

And say we had a time machine and went to next year the output would need to appear as such:

ID JOB_ID   RATE      START_DT     END_DT
43  41       11      06/06/2022 
42  42       12      06/06/2022 
65  43       19.5    06/22/2022 
44  45       15      06/06/2022 
45  46       16      06/06/2022 
46  47       19      06/06/2022 
47  48       20      06/06/2022 
48  49       25      06/06/2022

CodePudding user response：

Assuming that the data type of START_DT and END_DT is DATE you can compare them as you would any other data type. For example, using

SELECT *
  FROM SOME_TABLE
  WHERE SYSDATE >= START_DT AND
        (SYSDATE <= END_DT OR
         END_DT IS NULL)

or the equivalent

SELECT *
  FROM SOME_TABLE
  WHERE SYSDATE BETWEEN START_DT
                    AND NVL(END_DT, DATE '9999-12-31')

Even if the dates are stored as character strings you can still use nearly the same query:

SELECT *
  FROM SOME_TABLE_WITH_CHAR_DATES
  WHERE SYSDATE BETWEEN TO_DATE(START_DT, 'MM/DD/YYYY')
                    AND NVL(TO_DATE(END_DT, 'MM/DD/YYYY'), DATE '9999-12-31')

db<>fiddle here