Grouping by half hour intervals 1

[kbrearey]

Hi Everyone!

I am fairly new to Crystal Reports and am trying to figure out a way to group data in a specific way. What I need to get is a count of records that have a specific attribute set to a specific value and I need to have this count done in half hour intervals. My thoughts here are that I need to somehow be able to group by those half hour intervals. The DB that I am working with does not store dates/times as DATETIME data types, rather as separate integers in the following format:

Date: MMDDYYYY
Time: HHMMSS

I am not concerned with the date, because I can set the date as a group in and of itself. What I really need is a way to group not just by the time, but by the half hour interval of time.

I know that I will need to somehow compare the values for HH and make sure they are the same and MM and make sure they are between either (00 and 29) or (30 and 59), but I can't seem to get my head around how to do this. Any thoughts?

Thanks in advance to everyone!

 

[CoSpringsGuy]

first, if you havent already done so) convert your integers to time (assuming zero placeholders, ie 100300 = 10:30:00)
//integertotime
time(
int({timefield}/10000),//hh
int(remainder(remainder(timefield,10000),10000)/100),//mm
remainder(timefield,100)//ss
)


then create the second formula and group on it
//timegroup
int(
(hour({@integertotime})*60*60
+
minute({@integertotime})*60)
/1800
)

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Or you could use:

stringvar x := totext({table.number},"000000");
timevar y := time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)));
if y in time(hour,0,0) to time(hour,29,59) then
time(hour,0,0) else
if y in time(hour,30,0) to time(hour,59,59) then
time(hour,30,0)

Then group on the formula.

-LB

 

[CoSpringsGuy]

My solution will work but what I like better about the solution LBass provided is that you can display the formula itself in the group header (or footer), after grouping, to display the time grouping ...

Nice LBass!

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[kbrearey]

GREAT! This is exactly what I was looking for. I am going to try this now.

I appreciate your help, LBass and CoSpringsGuy!

 

[kbrearey]

I just tried it and it works BEAUTIFULLY! Thanks so much!