Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Working days between two dates Post Reply Post New Topic
Author Message
rob.morris
Newbie
Newbie


Joined: 09 Oct 2014
Online Status: Offline
Posts: 3
Quote rob.morris Replybullet 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       
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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.

Hopefully, this is clearer than mud.
IP IP Logged
z9962
Senior Member
Senior Member
Avatar

Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
Quote z9962 Replybullet Posted: 15 Dec 2014 at 3:26am
Note it does not check public holidays
 
you will need to change for days.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
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



This page was generated in 0.016 seconds.