Print Page | Close Window

Records submitted during specific time frames

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=20884
Printed Date: 05 May 2024 at 2:52pm


Topic: Records submitted during specific time frames
Posted By: SSLead
Subject: Records submitted during specific time frames
Date Posted: 15 Jul 2014 at 7:35am
I need to create a report that lists and provides a count for the number of records that have a submit date between 05:00 and 05:30, M-F.

The field TASKS.OPENDATE returns a result of 6/30/2014   9:17:59PM. The report currently prompts for the date range for this report. This will most likely be a montly report so I would select June 1st to June 30, 2014. Then the records selection needs to be narrowed down further to the 5:00-5:30 date range for Monday through Friday. I need to figure out this piece of it so that I can do a count for the number of records submitted in this timeframe.


Replies:
Posted By: DBlank
Date Posted: 15 Jul 2014 at 9:12am
tasks.opendate in lastfullmonth
and
datepart('w',tasks.opendate) in 2 to 6
and
time(tasks.opendate) in time(5,0,0) to time(5,30,0)

Posted By: SSLead
Date Posted: 15 Jul 2014 at 10:43am
This didn't work. Could the "PM" in the time format have anything to do with it?

Posted By: DBlank
Date Posted: 15 Jul 2014 at 11:22am
what exactly do you mean by not working?
this is for the record select statement
this is from 5 am to 5;30 am - if you wanted pm you need to chnage the formula to use those values... time(17,0,0)
the first condition is optional but limits the set to the last month in you do not want to use run time parameters
 
what is the field type for tasks.opendate?
if it is a string you can convert it to a datetime in each of the conditions
example
datetime({tasks.opendate}) in lastfullmonth
and
datepart('w',datetime({tasks.opendate})) in 2 to 6
and
time({tasks.opendate}) in time(5,0,0) to time(5,30,0)
 
if you are getting an actual error, what is the error you are getting and what is highlighted on it?

Posted By: SSLead
Date Posted: 16 Jul 2014 at 2:46am
It was returning 0 results. The issues was that I needed PM so I changed it to 17s and it worked. The following selection formula returned the desired results. Thank you so much for your assistance!!!
{TASKS.OPENDATE} = {?Request_Date_Range}
and
datepart('w',{TASKS.OPENDATE}) in 2 to 6
and
time({TASKS.OPENDATE}) in time(17,0,0) to time(17,30,0)

Posted By: SSLead
Date Posted: 16 Jul 2014 at 3:03am
I spoke too soon. After reviewing the results further I'm seeing that there is an issue. For example, one record has an open date of 7/8/14 at 1:28 PM but it is being displayed in the final report and it is listing this record with a time of 5:28. I am in the Eastern time zone so I'm not sure if there is some type of issue with that in the report.

Posted By: DBlank
Date Posted: 16 Jul 2014 at 3:52am
your time zone should not impact anything anything per se unless the source data is captured in one time zone and your analysis of it is desiring a different time zone. The source data is what the select statement is going to reference.
The select statment does not alter anything so it would have no effect on the display of the data.
How do you know the record in question 1:28 pm displaying as 5:28 is the same record?
are you viewing it in the report on a detail section with a unique identifier on that same detail section?
 

Posted By: SSLead
Date Posted: 16 Jul 2014 at 4:01am
I had the Task.Opendate as a column to be displayed in the report as well and that is where it is listing the 5:28 or other incorrect times. I realized that the data was incorrect because the analyst listed on some of the records in the report ends her shift at 4:30 and she had several listed in the 5-5:30 range. So I looked up one of the original records in our database to see that in fact the actual time was 1:28 and not 5:28. The others were displaying the same 4 hour difference. I'm not sure why it is adding 4 hours and including it in the report. These records shouldn't be listed at all nor should they have a four hour difference in time displayed.

Posted By: DBlank
Date Posted: 16 Jul 2014 at 4:16am
did you create this report from scratch or are you working on an existing report?

Posted By: SSLead
Date Posted: 16 Jul 2014 at 5:19am
I had dissected an existing report. After you asked though, I created it from scratch and got the same result.

Posted By: DBlank
Date Posted: 16 Jul 2014 at 6:35am
is your data source a table or a stored proc?
If you were comparing table data but your report source is a stored proc (or view) the stored proc may be running a date add onto a field, or something akin to that.

Posted By: SSLead
Date Posted: 16 Jul 2014 at 8:14am
That was the issue. The table I was using must have been adding something to it. I was able to locate the correct table and the report is running as desired. I was able to create a chart for the number of records that were submitted for each day.   I displayed a distinct count of the number of records returned in the report but would also like to display the number for the average number of records submitted per day. How do I do a formula for the average per day?

Posted By: DBlank
Date Posted: 16 Jul 2014 at 8:54am
maybe this?
formula1=date(datimefield)
average:
distinctcount(record_ID) / distinctcount(formula1)

Posted By: SSLead
Date Posted: 16 Jul 2014 at 10:28am
That worked! I think I have everything I need for now. Thanks!


Print Page | Close Window