Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Time Ranges

Status
Not open for further replies.

jacob94

Technical User
Dec 5, 2005
161
US
I have a database with the following data:

Date Time Count
1/1/06 12:30pm 1
1/1/06 1:30pm 1
1/1/06 3:30pm 1
1/2/06 4:30pm 1
1/2/06 4:45pm 1
1/2/06 8:30pm 1

I need to show the count for the earliest time for each date plus 1 hour. In this case 12:30pm is the earliest time for 1/1/06 and it should search between 12:30pm and 1:30pm because it is 1 hour. The count would be 2. For 1/2/06, the count should be 2 because 4:30pm was the earliest time and it should search between 4:30 and 5:30pm/

What is the best way to do this in CR 8.5. My DB does not support SQL expression fields.

Any ideas?

I am trying to display in CR 8.5 the following:
 
I am not sure if this is possible in CR alone?
 
Group by the date and use the 3 formula method as follows:

Group header formula:
whileprintingrecords;
datetimevar MyDate:= {table.date};
Numbervar Counter:=0;

Details suppression formula;
whileprintingrecords;
datetimevar MyDate;
Numbervar Counter;
If datediff("n",{{table.date},MyDate)) < 61 then
counter:=Counter+1

Group footer (for display, suppress the other sections):
whileprintingrecords;
Numbervar Counter

-k
 
Try a formula like this, assuming you have inserted a group on {table.datetime} at the daily level:

//{@reset} to be placed in the date group header:
whileprintingrecords;
numbervar y := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar y;

if {table.datetime} in minimum({table.datetime},{table.datetime}) to
dateadd("h",1,minimum({table.datetime},{table.datetime})) then y := y + 1; //or {table.count} if that can be more than 1

Then in the group footer place;
//{@display}:
whileprintingrecords;
numbervar y;

-LB
 
LB's will give you the wrong data, as Crystal will return 1 hour, 59 minutes and 59 seconds as 1 for the datediff("h") function.

Otherwise the formula is fine, you'd just need to change it to check for under 61 minutes using the n (minutes), not that it's within 1 hour.

-k
 
SV-Please note that I did not use datediff.

-LB
 
Thanks for the responses...

I made a mistake on my first post. There are not two fields for date and time, it is just one field called datetime (1/1/06 12:30pm)

Is that what I use in your examples?

Please let me know.
 
For my suggestion, just plug your datetime field into the formula wherever it says {table.datetime}. You should also have inserted a group on the datetime field.

-LB
 
I did that and LB's example worked perfectl;y. That is amazing...

SV's example did not reset after the 60 mins and did a total count? Not sure why.


Thanks!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top