Oracle: Return the specific records based on one column date-CodePudding

I have a database structure as below.

period	month	start_date
1	April	2022-04-01
2	May	2022-05-07
3	June	2022-06-04
4	July	2022-07-02
5	August	2022-08-06
6	September	2022-09-03
7	October	2022-10-01
8	November	2022-11-05
9	December	2022-12-03
10	January	2023-01-01
11	February	2023-02-04
12	March	2023-03-04

End date of the year is 2023-03-31.

Based on current_date, how do I select the query to return where the current date falls under Period 6.

My current query as below.

SELECT period FROM table1 as a
WHERE
a.start_date = (SELECT MAX(start_date) FROM table1 as b WHERE 
b.start_date <=current_date) and ROWNUM <= 1

Is there anyway to improve the current query which to avoid using subquery?

CodePudding user response：

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>

CodePudding user response：

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))
                 ELSE NEXT_DAY(TRUNC(ADD_MONTHS(start_date, 1)) - 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:

PERIOD	MONTH	START_DATE
6	September	2022-09-03 00:00:00

fiddle