Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Records submitted during specific time frames Post Reply Post New Topic
Page  of 2 Next >>
Author Message
SSLead
Newbie
Newbie


Joined: 15 Jul 2014
Online Status: Offline
Posts: 18
Quote SSLead Replybullet Topic: Records submitted during specific time frames
    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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)
IP IP Logged
SSLead
Newbie
Newbie


Joined: 15 Jul 2014
Online Status: Offline
Posts: 18
Quote SSLead Replybullet Posted: 15 Jul 2014 at 10:43am
This didn't work. Could the "PM" in the time format have anything to do with it?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?


Edited by DBlank - 15 Jul 2014 at 11:23am
IP IP Logged
SSLead
Newbie
Newbie


Joined: 15 Jul 2014
Online Status: Offline
Posts: 18
Quote SSLead Replybullet 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)
IP IP Logged
SSLead
Newbie
Newbie


Joined: 15 Jul 2014
Online Status: Offline
Posts: 18
Quote SSLead Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
 


Edited by DBlank - 16 Jul 2014 at 3:52am
IP IP Logged
SSLead
Newbie
Newbie


Joined: 15 Jul 2014
Online Status: Offline
Posts: 18
Quote SSLead Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Jul 2014 at 4:16am
did you create this report from scratch or are you working on an existing report?
IP IP Logged
SSLead
Newbie
Newbie


Joined: 15 Jul 2014
Online Status: Offline
Posts: 18
Quote SSLead Replybullet 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.
IP IP Logged
Page  of 2 Next >>
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.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.