Joined: 09 Oct 2014
Online Status: Offline
Posts: 3
Topic: Working days between two dates Posted: 12 Dec 2014 at 2:17am
I need to set up a report that shows me the number of working days between the first and second instance of a customer calling up to chase delivery.
The way my report is set up is that I have a group header based on the Property Reference Number.
Underneath this in the details box I have WorkID number, date logged, and officer.
What I would like to do is create a formula to check on the difference in working days between job 1 for the property, and job 2 for the same property.
Below is a crude example of the layout of the report
(Group Header)1 High Street, A Town, A City
(Details) Job Number WorkID Date Logged Officer Working Days
1 123456 1/12/2014 AB n/a
2 123457 10/12/2014 AB 7
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Posted: 12 Dec 2014 at 6:20am
this is always a difficult calculation...if you have a calendar table, it would probably include a flag for weekend or some such and you could just count the days (I am probably assuming that you are using a stored proc...I shouldn't)
Without that, what I would do, is count the number of days between the 2 dates. If the difference is more than 7, I would remove 2 days for each group of 7 more....so 10 days difference would result in removing just 2 days.
Here's the tough part, obvious is it is less than 7 or for many cases, there is weekend between the start and end dates, so I would check the day of week for the start and end dates...and if the end day of week is less than the start, I would subtract 2 more days. I believe that Sunday is 1 and Sat=6, so Fri=5 and Mon=2. If the start is 5 and then end is 2 and the date diff is 4, then it was only 2 working days.
you would apply the same type of logic if the date diff was 11, just you would remove 4 days...2 for the group of 7 and 2 for the dayOfWeek difference.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum