CODEWITHSUNDEEP
sqloracle-databaseplsql
MichalAndrzej
MichalAndrzej

Reputation: 77

Oracle SQL - How to return common date period from database (based on start/end dates)

I'm struggling with returning common period for start and end dates for specific Limit_ID. My table looks like this:

Code Start_Date End_Date Limit_ID
912 08/01/2022 14/01/2022 44
905 10/01/2022 12/01/2022 44
904 06/01/2022 09/01/2022 NULL
410 09/01/2022 20/01/2022 44

Base query looks like below, it's working but only when time periods have common part.

SELECT o.limit_id
      ,MAX(start_date) date_from
      ,MIN(end_date)   date_to
  FROM table1 e
  JOIN table2 o
    ON e.code= o.code
 WHERE o.limit_id = 44
 GROUP BY e.limit_id

To make it easier to understand currently it's look like this (I'm getting correct period: 10/01 - 12/01) Periods

But it doesn't work correctly for case when Code 904 will include Limit_ID = 44. Then I'll expect query to return 0 rows (as there is no common period). So my question is - is there a nice way to return results only if date ranges have common period?

Thanks

Upvotes: 0

Views: 174

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

You should check if max(start_date) is lower than min(end_date)

SELECT o.limit_id
  ,MAX(start_date) date_from
  ,MIN(end_date)   date_to
  FROM table1 e
  JOIN table2 o
    ON e.code= o.code
 WHERE o.limit_id = 44
 GROUP BY e.limit_id
 HAVING MAX(start_date)<MIN(end_date)

Upvotes: 3

Related Questions