Date Time difference in days excluding weekends

[4programming]

Hello experts,
I need to know how to exclude weekends in days from this case statement..... I need it to work with the case statement as I don;t want to adjust my whole code. For example

ie. if Date 1 = May 2, 2008 and date 2 = May 5, 2008
Looking at this example the day difference = 3 days. Now my issue is how do I remove the weekends from this count so that the count in this example = 1 and not 3........?

(CASE WHEN (from_epoch(Date) - from_epoch(Date2)) >=2 
OR (From_Epoch(Date1) IS NULL and From_Epoch(Date) > sysdate + 2)  THEN 
'No' ELSE 'Yes' END)TargetNotMet

Thanks.

 

[ChrisHunt]

Assuming the start and end dates are always weekdays, you'd calculate the difference like this:

CREATE OR REPLACE FUNCTION workdays_between (d1 IN DATE,
                                             d2 IN DATE) RETURN NUMBER IS
   diff NUMBER;
BEGIN
   diff := TRUNC((d2-d1)/7)*5    -- Number of whole weeks * 5 days
         + MOD(d2-d1,7);         -- PLUS left-over days.
         
   -- If d2 is an earlier day of the week than d1, there must be a
   -- weekend between them, so knock off 2 days.
   
   IF TO_CHAR(d2,'D') < TO_CHAR(d1,'D') THEN
      diff := diff - 2;
   END IF; 

   RETURN diff;

END workdays_between;
/

You could put this calculation into your CASE statement (you'd need to use a further CASE or DECODE statement to do the day of the week check), but I think it better to encapsulate the calculation into a function - making it easier to maintain and re-use.

A function will also make it easier to deal with cases where start or end is on a weekend (in which case you've got to decide what it should do, your answers may differ from mine). You can also add refinements like discounting public holidays as well as weekends.

I have posted a package of working day functions at

http://www.extraconnections.co.uk/code/workdays

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd