Time Ranges

[jacob94]

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:

 

[jacob94]

I am not sure if this is possible in CR alone?

 

[synapsevampire]

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

 

[lbass]

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

 

[synapsevampire]

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

 

[lbass]

SV-Please note that I did not use datediff.

-LB

 

[synapsevampire]

Ahhh, sorry, LB thought that you had.

-k

 

[jacob94]

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.

 

[lbass]

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

 

[jacob94]

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!