Crystal Reports Stuck!!!!!! - Reporting against a 12 hour clock

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Stuck!!!!!! - Reporting against a 12 hour clock

Author	Message
Jloyd666 Newbie Joined: 18 Mar 2011 Location: United Kingdom Online Status: Offline Posts: 7	Topic: Stuck!!!!!! - Reporting against a 12 hour clock Posted: 06 Mar 2012 at 3:12am
	Help!!! I have two dates. They can be months and years appart. There in a DD/MM/YY HH/MM/SS format. I need to tell the minuites between the two. However a whole days is only from 07:00 to 19:00. So I want the clock to count only while the time is between those parameters and also when the date isnt a weekend. I would appreciate any suggestions as to how best to tackle this, however I aonly have crystal functions to achieve this. Regards
	IP Logged


lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 06 Mar 2012 at 7:50am
	probably what I would start with finding the number of days between the 2 dates: datediff("d", day1, day2) I would also find the day of the week for each date...this would be used to determine the number of weekends to subtract from your datediff. You might try find the differences in weeks, but I am not sure how it calculates the weeks. Either way, find the total number of days difference. I think that I would try finding the difference of the times for the same date...put the starting time on a date..any date, and the ending time on the same date, this will give you a number of minutes. Then you can take the number of days (already adjusted for weekends) x ( 12 60) [24-12 for the hours of operations] +- time diff. At least that is the approach that I would attempt. If it doesn't succeed, perhaps it will yield insights as to how to proceed. HTH
	IP Logged

kostya1122 Senior Member Joined: 13 Jun 2011 Online Status: Offline Posts: 475	Posted: 06 Mar 2012 at 8:27am
	how about in select expert time(table.date) >= time(7,0,0) and time(table.date) <= time(19,0,0) and dayofweek(table.date ) <> ["6","7"] and table.date in datetime() to datetime() then create a formula like datediff("n",startdatetime,enddatetime)
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 06 Mar 2012 at 8:56am
	but will that give the number of minutes between 2 random dates, not counting the time not worked... I think jloyd666 is trying to get the time that an issue was worked on...and they don't work after 7 or on weekends. I've been wrong before...
	IP Logged

Jloyd666 Newbie Joined: 18 Mar 2011 Location: United Kingdom Online Status: Offline Posts: 7	Posted: 06 Mar 2012 at 10:16pm
	If anyone is curious this is due to different SLA's on our service management tool. We had a clock feature which we thought would take into accout the SLA's of the service and then provided the accurate working time. We now know that this isnt true. So what I have is an incident ticket that has been open between two dates and I need to workout the time it was open if it has a bronze SLA (7am - 7pm and exluding weekends). Because of the way the tool works and my lack of SQL experience I only really have the tool in crystal to do this. thanks for the pointers its all very useful
	IP Logged

rkrowland Senior Member Joined: 20 Dec 2011 Location: England Online Status: Offline Posts: 259	Posted: 07 Mar 2012 at 3:01am
	DateTimeVar startdate := {table.datefield1}; DateTimeVar enddate := {table.datefield2}; Numbervar weekdays:= DateDiff ("d", startdate, enddate) - DateDiff ("ww", startdate, enddate, crSaturday)- DateDiff ("ww", startdate, enddate, crSunday); //weekdays * 12 //Hours weekdays * 720 //Minutes Based on Lockwelle's post, that should give you the correct number required. Regards, Ryan. Edited by rkrowland - 07 Mar 2012 at 3:03am
	IP Logged

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