What exactly is your definition of "working day"? Are public holidays working days? Does your business close for other reasons such as summer/winter shutdown? CEO's birthday?
The easiest way to solve these kinds of issues is to make a dates table. This stores a flag saying whether a date was "working" or not:
create table calendar_dates (
calendar_date date not null primary key
check (calendar_date = trunc(calendar_date)),
is_working_day varchar2(1) not null
check (is_working_day in ('Y', 'N'))
);
insert into calendar_dates
with dates as (
select date'2016-12-27'+rownum dt
from dual
connect by level <= 60
)
select dt,
case
when dt = date'2017-01-02' then 'N'
when to_char(dt, 'dy') in ('sat', 'sun') then 'N'
else 'Y'
end
from dates;
commit;
Then finding the "last N" is simply a matter of returning the top-N rows <= today that have the working day flag set:
select calendar_date, to_char(calendar_date, 'dy') from (
select * from calendar_dates
where calendar_date <= date'2017-01-04'
and is_working_day = 'Y'
order by calendar_date desc
)
where rownum <= 5;
CALENDAR_DATE TO_CHAR(CALENDAR_DATE,'DY')
04-JAN-2017 00:00:00 wed
03-JAN-2017 00:00:00 tue
30-DEC-2016 00:00:00 fri
29-DEC-2016 00:00:00 thu
28-DEC-2016 00:00:00 wed
If a day changes from working -> not working or vice-versa, just update your data:
update calendar_dates
set is_working_day = 'Y'
where calendar_date = date'2017-01-02';
select calendar_date, to_char(calendar_date, 'dy') from (
select * from calendar_dates
where calendar_date <= date'2017-01-04'
and is_working_day = 'Y'
order by calendar_date desc
)
where rownum <= 5;
CALENDAR_DATE TO_CHAR(CALENDAR_DATE,'DY')
04-JAN-2017 00:00:00 wed
03-JAN-2017 00:00:00 tue
02-JAN-2017 00:00:00 mon
30-DEC-2016 00:00:00 fri
29-DEC-2016 00:00:00 thu
No code changes needed!