Oracle SQL - search for entries inside the same month but with different start/end dates

Question

I want to query for all entries in the table 'unique_delivery_locations' where 'transaction_identifier' is the same, and we are within the same month. The only difference should be in 'delivery_start_date' and 'delivery_end_date'. These could be different within the same month, some will start at the beginning of the month, and some in the middle.

I tried the following sequel, but I got the error:

ORA-00904: "MONTH": invalid identifier

SELECT *
FROM unique_delivery_locations
WHERE transaction_identifier IN (
        SELECT transaction_identifier
        FROM unique_delivery_locations
        WHERE MONTH(delivery_start_date) = MONTH(delivery_end_date)
        GROUP BY transaction_identifier
        HAVING COUNT(DISTINCT MONTH(delivery_start_date)) > 1
        );

Answer 1

As far as I understood, you want all the records from unique_delivery_locations when delivery_start_date is the same month as delivery_end_date.

Given that your delivery dates are in string format, we need to convert them, to date format first.

Try the code below:

SELECT 
    *
FROM 
    unique_delivery_locations t1
WHERE
    trunc(to_date(t1.delivery_start_date, 'yyyy-mm-dd'), 'mm') = trunc(to_date(t1.delivery_end_date, 'yyyy-mm-dd'), 'mm')