Thanks for posting the sample data. Don't forget to post the exact results you want from that data. It's great to describe the results, but describe them in addition to (not instead of) actually showing them.
Are these the results you want from the sample data (when run in the quarter starting July 1, 2020)?
X Y C_OR_P
------- ----------- --------
3 01-Apr-2020 Previous
4 10-May-2020 Previous
5 01-Jul-2020 Current
6 01-Jul-2020 Current
7 08-Jul-2020 Current
Here's one way to get them.
WHEN y >= TRUNC (SYSDATE, 'Q')
END AS c_or_p
FROM m_test m
WHERE y >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q')
AND y < ADD_MONTHS ( TRUNC (SYSDATE, 'Q')
ORDER BY y
You already knew how to use ADD_MONTHS to find the beginning of the following quarter. You can use ADD_MONTHS the same way to find the beginning of the previous quarter.
There's no reason to use the dual table in this problem.
Thanks for providing the DDL and DML statements.
Please state your full Oracle database version.
Please show the output you expect after running your query.