Skip to Main Content
  • Questions
  • Calculating last 5 business working days

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Alok.

Asked: January 13, 2017 - 10:10 am UTC

Last updated: January 26, 2017 - 12:41 pm UTC

Version: latest

Viewed 1000+ times

You Asked

Hi Tom,

I want to get last 5 business days. If its friday today, I need from passed monday to this friday and if its monday today...last tuesday to this monday is required. Saturday and sunday need to skipped in calculating last 5 business days.

Thanks,
alok

and Chris said...

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!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 25, 2017 - 8:43 pm UTC

Thanks for the solution.

But I want to exclude the saturday and sunday only,not any other holiday. How it will be implemented without creating a table for dates?
Chris Saxon
January 26, 2017 - 12:41 pm UTC

I'd still go with the table of dates, but only setting Sat and Sun to not working. Much less work for you later if the business changes their mind!