List of IDs for from_date to end_date (other columns differ)-CodePudding

Yesterday I posted a question regarding a problem I had in oracle sql. But I still have one problem, which i forgot in my first question. My table also includes other columns for rows with the same DESC. Like this:

ID	Desc	FromDate	ToDate	Color
ID_01	A	08.2017	10.2020	Red
ID_02	B	02.2019	09.2029	Blue
ID_03	C	02.2014	02.2019	Black
ID_04	D	04.2010	01.2019	Yellow
ID_05	D	01.2019	09.2029	Green

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!

CodePudding user response：

select "Date"
       ,ID
       ,"Desc"
from        (
            select "Date"
                   ,ID
                   ,"Desc"
                   ,row_number() over(partition by "Desc" order by ID) as rn
            from t 
            unpivot
           ("Date" for FDTD in ("FromDate","ToDate")) 
            where "Date" between date '2019-01-01' and date '2022-09-01'
           ) t
where rn = 1

Date	ID	Desc
01-OCT-20	ID_01	A
01-FEB-19	ID_02	B
01-FEB-19	ID_03	C
01-JAN-19	ID_04	D

Fiddle

CodePudding user response：

Given the sample data:

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

(and just focusing on the D values for DESC)

Then the accepted answer to the linked question:

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;

Only outputs a single DESC values for each day and there is only the corresponding minimum ID:

MONTH	ID	DESC
2019-01-01 00:00:00	ID_04	D
2019-02-01 00:00:00	ID_05	D
2019-03-01 00:00:00	ID_05	D
2019-04-01 00:00:00	ID_05	D
2019-05-01 00:00:00	ID_05	D
2019-06-01 00:00:00	ID_05	D
2019-07-01 00:00:00	ID_05	D
2019-08-01 00:00:00	ID_05	D
2019-09-01 00:00:00	ID_05	D

The reason that you get ID_04 for 2019-01-01 and then ID_05 for the rest of the days is that ID_04 has a toDate value of 2019-01-01 so it cannot be included in the range from 2019-02-01 through to 2019-09-01 as those months are outside the upper bound of its range. Instead, you get ID_05 for those months because they are within its range.

fiddle