I have table days table.There is oper_day column:

I want to output the maximum date available in a table that is less than the first date of each quarter for example: quarter_date: 01.10.2022 if 30.09.2022 has in a table I give 30.09.2022 else 29.09.2022 .How can I write query?

CodePudding user response：

You appear to be asking how to find the last date in each quarter, if so then GROUP BY the data TRUNCated to the start of the quarter and find the maximum date in each quarter (as it will be the latest date before the start of the next quarter).

If you want to ensure that the values are from quarters before the current quarter then you can add a WHERE filter to ignore values in the current quarter or later.

Like this:

SELECT TO_CHAR(TRUNC(oper_day, 'Q'), 'YYYY-Q') AS quarter,
       MAX(oper_day) AS last_oper_day_in_quarter
FROM   table_name
WHERE  oper_day < TRUNC(SYSDATE, 'Q')
GROUP BY TRUNC(oper_day, 'Q')

Which, for the sample data:

CREATE TABLE table_name (oper_day) AS
SELECT DATE '2022-01-01'   LEVEL - 1
FROM   DUAL
CONNECT BY DATE '2022-01-01'   LEVEL - 1 <= SYSDATE;

Outputs:

fiddle

CodePudding user response：

One could accomplish this by putting the dates into groups by quarter. Then finding the MAX date for that quarter.

SELECT   ADD_MONTHS(TRUNC(td.oper_day, 'Q'), 3) next_quarter_begin
        ,MAX(td.oper_day) max_date_prev_quarter
FROM     table_days td
GROUP BY ADD_MONTHS(TRUNC(td.oper_day, 'Q'), 3)
ORDER BY 1