Items Due within Working Day ranges....?

[MJRBIM]

CR-XI
Oracle 9i DB

We have a dataset with due dates that need to be compared to the current date to determine how many WORKING DAYS there are to complete the task.

WORKING DAYS are defined as all days Monday - Friday. We do not close for any holidays - just drop to a minimum staff.

The data looks like this....

ITEM DUE_DATE
=============================
123 2006/12/18
124 2006/12/19
125 2006/12/28
126 2006/12/22
127 2007/01/15
128 2006/12/29
129 2007/01/19
130 2007/02/14
=============================

We need to Group the results into the following ranges.

Due in 1-10 working days
Due in 11-20 working days
Due in 21-30 working days
Due in 31+ working days

I started working with some IF..THEN...ELSE formulas comparing the current date to the Due Date and then subtracting 2 from the count for each weekend based on the current Day of Week.

The formula quickly got HUGE and confusing...so I am wondering if anyone has a more elegant solution.

Thanks in advance.

 

[BobSuruncle]

This should work for you:

@WorkingDaysGroup

Local NumberVar wd ;
wd:= DateDiff('d',Currentdate,{Due_dates.Duedate})  //Number of days
- DateDiff('ww',Currentdate,{Due_dates.Duedate},6) //Subtract Saturdays
- DateDiff('ww',Currentdate,{Due_dates.Duedate},1) ;  //Subtract Sundays

select wd
case 1 to 10 : "Due in 1-10 Working days"
case 11 to 20 : "Due in 11-20 Working days"
case 21 to 30 : "Due in 21-30 Working days"
default : "Due in 31+ Working days"

Insert a group on this formula.

Bob Suruncle

 

[anglophobe]

To delete Saturday, change the

- DateDiff('ww',Currentdate,{Due_dates.Duedate},6)

to

- DateDiff('ww',Currentdate,{Due_dates.Duedate},7)

 

[BobSuruncle]

Oops. Thanks for that! 6 is Friday!

Bob Suruncle

 

[MJRBIM]

THANKS!