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

Danias1927 0
Viewed 39

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!

oracle sql
2 Answers
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

DannySlor0

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

MT00
Related