Grouping by half hour time interval

[oanion]

Using Crystal Reports 10. I’m trying to create a report that will group by time for each half hour. For instance, I want to show a report that has the following intervals and groups

1:00
1:30
2:00
2:30
3:00
3:30 ……

This should progress for a full 24 hour period. I went into the grouping options, however, it only allows grouping for each hour and only if the hours are within the list of times given. I need to show all times listed above regardless of whether there is a specific time associated with it. Does anyone know how I can accomplish this using a short and sweet formula?

 

[synapsevampire]

By whether there is a specific time associated with it, you must mean that you want to show the time when there isn't any data for that specific time period.

There are many approaches to doing this, I generally suggest creating a period table as it solves many of life's difficulties along these lines, check out my FAQ:

faq767-4532

Another alternative would be to create a Command Object with the datetime periods in it, and then left outer join that to your current data set based on the time alone, btu this is datbase dependent, and you didn't bother to post the basics.

Or you might hardcode out the time values as they don't exist in your recordset, and Crystal doesn't fabricate data for you.

As for a 1/2 hour grouping against valid data, try a formula of:

If Minute({table.datetime}) < 30 then
Time(Hour({DateField}),00,00)
else
If Minute({table.datetime}) >= 30 then
Time(Hour({DateField}),30,00)

-k

 

[oanion]

Thanks for the valuable information. The result that I need is the first one you suggested which is to show ALL the time periods within a 24 span with 30 minute intervals. I work in a call center, so the ultimate goal is to show the number of calls taken between each time period for a particular agent. For instance:

Agent Interval Calls Taken
Turner 17:00 2
17:30 0
18:00 6

Since I'm not a SQL Server Programmer, I'm not following the information in FAQ767-4532. Is there another approach that can be accomplished in Crystal?

Thanks

 

[synapsevampire]

There are a few ways one might do this, including hardcoding out 24 formulas. Your exampole shows nothing about the data, nnor what the required output is, if you just needed times for 24 hours, you could have typed them out by now. Please take the time to explain your requirements unless you only want to read theory.

You can use 24 formulas to then sum a field or whatever...:

whileprintingrecords;
numbervar T003000;
if time({table.datetime}) <= time(0,30,0) then
T003000:=T003000+{table.field}

But this does not allow for grouping the data, as you don't have 24 hours worth of data, so please don't confuse the terms and intent.

Again, you'll get better results if you spend a little time defining the requirements, I understrand your dilem (sadly shared by all of us), but I have little idea of what you are intending to do.

-k

 

[MrBillSC]

You could create the 48 formulas for each half hour of the day and set the value in each formula to 1 or 0 depending on the time of the call.

Then divide you group footer for agent into 48 subsections with the time for each subsection displayed in a text object. Enter the sum of 48 formulas into the appropriate footer subsections. This is a lot of work but I think it will give you what you want.

Maybe someone know a more elegant solution.

MrBill

 

[synapsevampire]

Oh, right, 48 formulas, even uglier...

As mentioned before, creating a complete recordset for the report is always desirable.

-k