create table m_test(x number, y date);
insert into m_test values(1,to_date('01-FEB-2020','DD-MON-YYYY'));
insert into m_test values(2,to_date('03-FEB-2020','DD-MON-YYYY'));
insert into m_test values(3,to_date('01-APR-2020','DD-MON-YYYY'));
insert into m_test values(4,to_date('10-MAY-2020','DD-MON-YYYY'));
insert into m_test values(5,to_date('01-JUL-2020','DD-MON-YYYY'));
insert into m_test values(6,to_date('01-JUL-2020','DD-MON-YYYY'));
insert into m_test values(7,to_date('08-JUL-2020','DD-MON-YYYY'));
commit;
I am trying to do 2 things,
1. Get data where the y column falls under the current quarter and previous quarter, which means 4/1/2020 - 9/30/2020, I wrote a query that handles give me data that falls in current quarter (below), how can I improvise to include previous quarter too? result should be records starting x column values 3 until 7, as the dates fall under current and previous quarter
SELECT * FROM m_test where y between (select trunc(sysdate,'Q') FROM DUAL )
AND (SELECT add_months(trunc(SYSDATE, 'q'), 3) -1 FROM DUAL)
2. I need to add a column that identifies if the record belongs to current quarter or previous and show "current" or "previous" accordingly, how do I do that ?