count business days in calculation

Continuing from my previous question: case when date column is greater than sysdate then ‘Y’

One thing I forgot to consider was including working days in the calculation.

Here is the query:

select
 v.voyage "Voyage"
 ,v.service "Service"
 ,to_char(vp.eta_date, 'MONTH dd, yyyy') "ETA"
 ,case 
   when v.service = 'USA' then to_char(vp.eta_date - 2, 'MONTH dd, yyyy')
   else 'n/a'
   end as "Notice"
 ,case 
   CASE WHEN v.service = 'USA' AND vp.eta_date - 2 > sysdate THEN 'Y' 
   ELSE 'N'
   end as "Sent"
from 
 table  

So I need to exclude Saturday and Sunday in the calculation above.

I tried to add this in the where clause:

to_char(vp.eta_date, 'DY') NOT IN ('SAT', 'SUN')

But the calculation does not seem to be working.

If I take vp.eta_date – 5, and I’ll use December 22, 2021 for example. I should get the following date:

December 16th

But I am getting December 17th.

This of course leads me to believe that what I tried is not working.

How do I fix this?

Answer

You can compare the date to the date at the start of the ISO week and then increase the number of days depending on which day of the week it is:

CASE
WHEN v.service   = 'USA'
AND  vp.eta_date - CASE TRUNC(vp.eta_date) - TRUNC(vp.eta_date, 'IW')
                   WHEN 0 THEN 4 -- Monday
                   WHEN 1 THEN 4 -- Tuesday
                   WHEN 2 THEN 2 -- Wednesday
                   WHEN 3 THEN 2 -- Thursday
                   WHEN 4 THEN 2 -- Friday
                   WHEN 5 THEN 2 -- Saturday
                   WHEN 6 THEN 3 -- Sunday
                   END
     > SYSDATE
THEN 'Y' 
ELSE 'N'
END as "Sent"