I have two date fields, DATE_FIELD_ONE = 8/30/2018 and DATE_FIELD_TWO = DATE_FIELD_ONE + 20. I need to find what DATE_FIELD_TWO should be if I'm only added 20 business days . How would I accomplish this? I thought maybe trying 'DY' but not sure how to get it to work. Thanks.
CASE WHEN TO_CHAR(TO_DATE(DATE_FIELD_ONE),'DY')='SAT' THEN 1 ELSE 0 END
CASE WHEN TO_CHAR(TO_DATE(DATE_FIELD_ONE),'DY')='SUN' THEN 1 ELSE 0 END
You may try this :
select max(date_field_two) as date_field_two
from
(
select date'2018-08-30'+
cast(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH')
in ('6','7') then
0
else
level
end as int) as date_field_two,
sum(cast(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH')
in ('6','7') then
0
else
1
end as int)) over (order by level) as next_day
from dual
connect by level <= 20*1.5
-- 20 is the day to be added, every time 5(#of business days)*1.5 > 7(#of week days)
-- 7=5+2<5+(5/2)=5*(1+1/2)=5*1.5 [where 1.5 is just a coefficient might be replaced a greater one like 2]
-- so 4*5*1.5=20*1.5 > 4*7
)
where next_day = 20;
DATE_FIELD_TWO
-----------------
27.09.2018
by using connect by dual clause.
P.S. Ignored the case for public holidays, which differ from one culture to another , depending on the question being related with only weekends.
Edit : Assume you have a national holidays on '2018-09-25' and '2018-09-26' (in this set of days), then consider the following :
select max(date_field_two) as date_field_two
from
(
select date'2018-08-30'+
(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH')
in ('6','7') then
0
when date'2018-08-30'+level in (date'2018-09-25',date'2018-09-26') then
0
else
level
end) as date_field_two,
sum(cast(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH')
in ('6','7') then
0
when date'2018-08-30'+level in (date'2018-09-25',date'2018-09-26') then
0
else
1
end as int)) over (order by level) as next_day
from dual
connect by level <= 20*2
)
where next_day = 20;
DATE_FIELD_TWO
-----------------
01.10.2018
which iterates one day next, as in this case, unless this holiday coincides with weekend.
you can define workdays to be whatever you like if you use a PL/SQL function
Have a simple prototype here - without any holidays - but it could be adapted for that purpose using the same kind of logic.
create or replace function add_business_days (from_date IN date, bd IN integer) return date as
fd date := trunc(from_date,'iw');
cnt int := (from_date-fd)+bd-1;
ww int := ceil(cnt/5);
wd int := mod(cnt,5);
begin
return from_date + (ww*7)+wd;
end;
/
I realize you already have an answer, but for what it's worth this is something we deal with all the time and have what has turned out to be a very good solution.
In effect, we maintain a separate table called "work days" that has every conceivable date we would ever compare (and that definition will vary from application to application, of course -- but in any case it will never be "huge" by RDBMS standards). There is a boolean flag that dictates if the date is a work day or a weekend/holiday, but more importantly there is an index value that only increments on work days. The table looks like this:
The advantage to this is transparency and scalability. If you want the difference between two dates in work days:
select
h.entry_date, h.invoice_date, wd2.workday_index - wd1.workday_index as delta
from
sales_order_data h
join util.work_days wd1 on h.sales_order_entry_dte = wd1.cal_date
join util.work_days wd2 on h.invoice_dte = wd2.cal_date
If you need to take a date in a table and add 20 days (like your original problem statement):
select
h.date_field_1, wd2.cal_date as date_field_1_plus_20
from
my_table h
join util.work_days wd1 on h.date_field_1 = wd1.cal_date
join util.work_days wd2 on
wd1.workday_index + 20 = wd2.workday_index and
wd2.is_workday
(disclaimer, this is in PostgreSQL, which is why I have the boolean. In Oracle, I'm guessing you need to change that to an integer and say =1)
Also, for the bonus question, this also gives two different options for defining "work day," one that rolls forward and another that rolls backwards (hence the workday_index and workday_index_back). For example, if you need something on a Saturday, and Saturday is not a work day, that means you need it on Friday. Conversely, if something is to be delivered on Saturday, and Saturday is not on a work day, then that means it will be available on Monday. The context of how to handle non-workdays differs, and this method affords you the option of chosing the right one.
As a final selling point, this option allows you to define holidays as non-work days also... and you can do this or not do this; it's up to you. The solution permits either option. You could theoretically add two more columns for work day index weekend only that gave you both options.
