Time ranges
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1956
Printed Date: 02 May 2024 at 4:47am
Topic: Time ranges
Posted By: Kathleen
Subject: Time ranges
Date Posted: 31 Dec 2007 at 6:19am
|
Hello, new Crystal User again. My next problem is that I need to report a number of "situations" per shift of work. Meaning, from 7:00 am to 3:00 pm. I need to count "incidents" then count from 3:00 pm to 11:00pm - - and then from 11:00 pm to 7:00am.
I created formulas that ask for the information between these times, but when I double check the information, I'm not getting the correct numbers. Can someone help me out with a formula to more accurately obtain this information? I am using greater than and less than but the 7:00 am to 3:00 pm still shows zero incidents.
|
Replies:
Posted By: BrianBischof
Date Posted: 31 Dec 2007 at 4:17pm
I'm having trouble visualizing exactly where the problem would be, so it might help if you expand on the problem or possible show the record selection formula you are using.
What I'm wondering is whether you are including the data with the time range as well. Is is possible that the "less than" condition is miscalculating due to the fact that the field could also be taking the date into account and the change of days between times is throwing it off?
-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
Posted By: Kathleen
Date Posted: 02 Jan 2008 at 6:08am
|
In the first running total below, I am trying to calculate the number of incidents that take place between 3:00 pm and 11:00 pm (second shift). I was told to try this calculation and I'm getting numbers, but I'm not exactly sure how this calculation knows to count "incidents".
ctime (#03:00 pm#)< mailto:%7b@time - {@time } and ctime (#11:00 pm#)>{@time}
In the next running total below, I am trying to calculate the number of incidents that take place between 7:00 am and 3:00 pm (first shift). I was told to try this calculation and I'm getting numbers for second and third shift, but nothing for first shift. First shift is where the number should be the highest - and the numbers I'm getting for second and third shift aren't correct either.
({Incident.IncTime}>"07:00 AM" and {Incident.IncTime}<"03:00 PM")
What I really want to do is count the number of incident DTS that occur between the time periods of each shift so I can compare which shift has the most incidents and see if there is a trend.
I so appreciate any assistance you can be with this. Being new, some of these calculations are over my head.
Thanks.
|
Posted By: Lugh
Date Posted: 02 Jan 2008 at 8:22am
As an extremely critical question, what kind of values are your @time and IncTime fields? Are they Time values, or, more likely, DateTime values?
The simplest solution is to make sure you are using CTime on both sides of the comparison. If you are using DateTime values, this will pull out only the time value. However, that does mean that it will ignore the date value. This may be an issue for the shift that straddles midnight.
As another tip, make sure you are including one (and only one) of your endpoints. With your current formulas, you'll never capture any incidents that occur at exactly 3:00 pm.
Also, I'd recommend using the range data type, or using the BETWEEN operator, rather than using "less than" and "greater than" operators.
So, for your running total, try:
CTime(@time) IN CTime(#15:00#) TO_ CTime(#23:00#)
Look at the critical changes here. By using CTime on @time, we make sure we are only looking at the time piece of the value. "IN" and "TO" are part of the range operation. By putting the underscore after "TO" we are telling it to ignore 23:00 (i.e., consider the first time to be part of this shift, but the last time to be part of next shift). The military time is just personal preference on my part. I consider it to be less error-prone than trying to use "am" and "pm." But, then, I work with a bunch of military types.
To answer the other question you snuck in there, the running total doesn't know it's counting incidents. It just knows that it's counting rows. Every time a new row pops up in that time frame, it adds 1 to the current total.
In your second formula, I have a sneaking suspicion that Crystal might not be doing the implicit conversion in the way you expect. If it is converting IncTime to a string, instead of "07:00 AM" to a time, you are asking which one comes first alphabetically. Now, 9 times out of 10, it works out to be the same. But, occasionally, it's just not. Especially as you haven't explicitly told Crystal what format to use for the conversion. While you're trying to get a handle on things, it is best to always use explicit conversions. So, try something like:
CTime({Incident.IncTime}) BETWEEN CTime("07:00") AND CTime("15:00")
I went with the BETWEEN structure here just to demonstrate it. However, notice something. There is no way to tell it to ignore the endpoints. If you use BETWEEN, then any incident which occurs at exactly 15:00 will get counted on two different shifts.
As a tip to help you as a newbie, try breaking the problem down into smaller pieces, and attacking it that way. Create a formula that just returns CTime(Incident.IncTime), and display it on the report. Display your running total on the report. Try to narrow down which specific piece of the formula is returning an unexpected value. This will greatly help you to correct whatever mistake you might have made.
|
Posted By: Kathleen
Date Posted: 07 Jan 2008 at 3:02pm
|
Hello and thanks for your recent response. I do have the CTime values counting, but I still have one issue. I have changed everything to military time (easier, you're right), however, I cannot get the formulas to count over the midnight time. Meaning, I have a shift that goes from 23:00 to 07:00, but for some reason, the formula CTime(@time) IN Ctime ("23:00") to_ Ctime(#07:00#) is not reading all the information.
I run test information and the items from 23:00 to 00:00 are not being counted in this calculation but all the others from 00:00 to 07:00 are being counted.
Have any more great advice for me?
|
Posted By: Lugh
Date Posted: 08 Jan 2008 at 4:11am
Try breaking it up into two ranges.
CTime(@time) IN CTime("23:00") TO_ CTime("24:00")
OR
CTime(@time) IN CTime("00:00") TO_ CTime("07:00")
|
|