how to get last businessday of last month in oralce-CodePudding

I have data like this my table

2020-01-01 H

2020-01-02 B

2020-01-03 B

2020-01-04 B

2020-01-29 B

2020-01-30 H

2020-01-31 H

2020-01-02 H

2020-02-02 H

2020-02-03 B

2020-02-04 B

2020-02-05 B

now my problem is in the current month i need to check third business day i.e in this case 2020-02-05 i need to get last business day of last month. i.e.2020-01-29

CodePudding user response：

in the current month I need to check third business day

From Oracle 12, you can use:

SELECT date_value
FROM   table_name
WHERE  TRUNC(SYSDATE, 'MM') <= date_value
AND    date_value < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)
AND    day_type = 'B'
ORDER BY date_value ASC
OFFSET 2 ROWS
FETCH NEXT ROW ONLY;

Which, for the sample data:

CREATE TABLE table_name (date_value, day_type) AS
SELECT DATE '2020-01-01', 'H' FROM DUAL UNION ALL
SELECT DATE '2020-01-02', 'B' FROM DUAL UNION ALL
SELECT DATE '2020-01-03', 'B' FROM DUAL UNION ALL
SELECT DATE '2020-01-04', 'B' FROM DUAL UNION ALL
SELECT DATE '2020-01-05', 'B' FROM DUAL UNION ALL
SELECT DATE '2020-01-28', 'B' FROM DUAL UNION ALL
SELECT DATE '2020-01-29', 'B' FROM DUAL UNION ALL
SELECT DATE '2020-01-30', 'H' FROM DUAL UNION ALL
SELECT DATE '2020-01-31', 'H' FROM DUAL UNION ALL
SELECT DATE '2020-01-02', 'H' FROM DUAL UNION ALL
SELECT DATE '2020-02-02', 'H' FROM DUAL UNION ALL
SELECT DATE '2020-02-03', 'B' FROM DUAL UNION ALL
SELECT DATE '2020-02-04', 'B' FROM DUAL UNION ALL
SELECT DATE '2020-02-05', 'B' FROM DUAL;

If the current month was 2020-01 then the output is:

DATE_VALUE
04-JAN-20

I need to get last business day of last month

SELECT date_value
FROM   table_name
WHERE  TRUNC(SYSDATE, 'MM') <= date_value
AND    date_value < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)
AND    day_type = 'B'
ORDER BY date_value DESC
FETCH FIRST ROW ONLY;

If the current month is 2020-01 then the output is:

DATE_VALUE
29-JAN-20

fiddle

CodePudding user response：

By adding 2 columns:

row_number() over(partition by trunc(date_value,'MM'), day_type order by date_value) as rn_month_asc, 
row_number() over(partition by trunc(date_value,'MM'), day_type order by date_value desc) as rn_month_desc

in a month the 3rd business day will have rn_month_asc=3 and day_type ='B' and the latest business day will have rn_month_desc=1 and day_type ='B', and easy to query other situations if you need to.