|
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
|
|
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.
Hopefully, this is clearer than mud.
|
IP Logged
