Oracle: Return the specific records based on one column date

Today is September 22nd, so - would this do?

Some sample data:

SQL> with test (period, month, start_date) as
  2    (select  1, 'april'    , date '2022-04-01' from dual union all
  3     select  5, 'august'   , date '2022-08-06' from dual union all
  4     select  6, 'september', date '2022-09-03' from dual union all
  5     select  7, 'october'  , date '2022-10-01' from dual union all
  6     select 10, 'january'  , date '2023-01-01' from dual union all
  7     select 12, 'march'    , date '2023-03-04' from dual
  8    ),

Query begins here:

  9  temp as
 10    (select period, month, start_date,
 11       row_number() over (order by start_date desc) rn
 12     from test
 13     where start_date <= sysdate
 14    )
 15  select period
 16  from temp
 17  where rn = 1
 18   /

    PERIOD
----------
         6

SQL>

It still uses a subquery (or a CTE, as in my example), but - as opposed to your approach, it selects from the source table only once, so performance should be improved.

A few more tests: instead of sysdate (line #13), presume that today is September 2nd (which means that it is in period #5):

  9  temp as
 10    (select period, month, start_date,
 11       row_number() over (order by start_date desc) rn
 12     from test
 13     where start_date <= date '2022-09-02'
 14    )
 15  select period
 16  from temp
 17  where rn = 1;

    PERIOD
----------
         5

SQL>

Or, if today were August 7th:

  9  temp as
 10    (select period, month, start_date,
 11       row_number() over (order by start_date desc) rn
 12     from test
 13     where start_date <= date '2022-08-07'
 14    )
 15  select period
 16  from temp
 17  where rn = 1;

    PERIOD
----------
         5

SQL>

Your rule for the start_date appears to be:

• If the month is January (first month of the calendar year) or April (typically, first month of the financial year) then use the 1st of that month;
• Otherwise use the 1st Saturday of the month.

If that is the case then you can calculate the start date of the next month and use the query:

SELECT *
FROM   table1
WHERE  start_date <= SYSDATE
AND    SYSDATE < CASE
                 WHEN EXTRACT(MONTH FROM ADD_MONTHS(start_date, 1))
                      IN (1, 4) -- 1st month of calendar or financial year
                 THEN TRUNC(ADD_MONTHS(start_date, 1), 'MM')
                 ELSE NEXT_DAY(TRUNC(ADD_MONTHS(start_date, 1), 'MM') - 1, 'SATURDAY')
                 END

Then, for your sample data:

CREATE TABLE table1 (
  period     NUMBER(2,0),
  month      VARCHAR2(9)
             GENERATED ALWAYS AS (
               CAST(
                 TO_CHAR(start_date, 'FXMonth', 'NLS_DATE_LANGUAGE=English')
                 AS VARCHAR2(9)
               )
             ),
  start_date DATE
);

INSERT INTO table1 (period, start_date)
  SELECT LEVEL,
         CASE
         WHEN EXTRACT(MONTH FROM ADD_MONTHS(DATE '2022-04-01', LEVEL - 1))
              IN (1, 4) -- 1st month of calendar or financial year
         THEN ADD_MONTHS(DATE '2022-04-01', LEVEL - 1)
         ELSE NEXT_DAY(ADD_MONTHS(DATE '2022-04-01', LEVEL - 1) - 1, 'SATURDAY')
         END
  FROM   DUAL
  CONNECT BY LEVEL <= 12;

Outputs:

fiddle