I have the following table:

And i would need the following result in oracle sql (from 01.2019 to 09.2022):

So the ID should be in the list, if the Date is between the FromDate and ToDate. If two IDs with the same Desc (like ID_04 and ID_05 for 01.2019) are valid, then the lower ID should be taken.

Thank you very much for your help!

Best regards

CodePudding user response：

Generate a calendar for your date range and then join it to your table and the GROUP BY the month and the desc (note: DESC is a reserved word meaning descending order and should not be used as an identifier):

WITH calendar (month) AS (
  SELECT ADD_MONTHS(DATE '2019-01-01', LEVEL - 1)
  FROM   DUAL
  CONNECT BY ADD_MONTHS(DATE '2019-01-01', LEVEL - 1) <= DATE '2019-09-01'
)
SELECT month,
       MIN(id) AS id,
       "DESC"
FROM   calendar c
       INNER JOIN table_name t
       ON (c.month BETWEEN t.fromdate and t.todate)
GROUP BY month, "DESC"
ORDER BY month, id

Which, for the sample data:

CREATE TABLE table_name (ID, "DESC", FromDate, ToDate) AS
  SELECT 'ID_01', 'A', DATE '2017-08-01', DATE '2020-10-01' FROM DUAL UNION ALL
  SELECT 'ID_02', 'B', DATE '2019-02-01', DATE '2029-09-01' FROM DUAL UNION ALL
  SELECT 'ID_03', 'C', DATE '2014-02-01', DATE '2019-02-01' FROM DUAL UNION ALL
  SELECT 'ID_04', 'D', DATE '2010-04-01', DATE '2019-01-01' FROM DUAL UNION ALL
  SELECT 'ID_05', 'D', DATE '2019-01-01', DATE '2019-09-01' FROM DUAL;

Outputs:

fiddle

CodePudding user response：

Thank you very much for your answer, it already works great. But I still have one problem, which i forgot in my first post. My table also includes other columns for rows with the same DESC. Like this:

This is the reason why I still get both IDs (4 and 5) with the posted sql. How can I prevent this in the best way?

Thank you!