How to make 30 minute interval data to hourly data 1

[tawild71]

I am using Crystal Reports 9. I am creating a report using a table that takes data and writes it to the IAgentQueueStats table every 30 minutes. The report is grouped by:

Group 1. Agent Name
Group 2. Starting interval date
Group 3. Workgroup
Group 4. Time in seconds

I need to be able to take the records with the date/time of 30 minutes and add it to the records with the date/time of 00 minutes or hourly values. This will create an hourly report. I am trying to manipulate a table that writes data every 30 minutes into a hourly report. The new report will show hourly values for each agent.

dIntervalStart is the date/time field I am using

I know I can get the hour, minute, and seconds out of the dIntervalStart field, but I am not sure how to combine the second values. In the report, the time will be reported in seconds.

I want to know the formula in Crystal Report Syntax that will take the time in seconds from table records that have the 30 minute time value and add it to the records that have the 00 minute time value with the same hour value.

Thank you for any help you can provide

 

[lbass]

If {IAgentQueueStats.dIntervalStart} is a datetime, you can just insert a group on this field and choose "This section will be printed: for each hour."

-LB

 

[tawild71]

I'm sorry I did not mention the second values for each Agent in the IAgentQueueStats table. I need to take the values from the record that recorded it's date at 30 minutes and add it to the record that was recorded at the 00 hour. (example: I need the agent's calls received, average talk, total talk, average after call work, total acw, which are in seconds, to be added to a record with time value of 1:00:00 from records that were created at 1:30:00). For the calls received formula I have...

AnsIncDirGroup4 is in the Group Footer @Group4
Sum({AgentQueueStats.nAnsweredAcd}
,{@Group4} )

@Group4
Time({AgentQueueStats.dIntervalStart})

For the average talk formula I have...

@AvgACDGroup4
numberVar Seconds := 0;

if Sum({AgentQueueStats.nAnsweredAcd},{@Group4}) > 0 Then
Seconds := Sum({AgentQueueStats.tTalkAcd}, {@Group4})
/ Sum({AgentQueueStats.nAnsweredAcd}
, {@Group4})
Else Seconds:=0;

How do I write a formula to handle this issue?

Thanks,

TW

 

[lbass]

If you do as I suggested, then you should be able to insert summaries at the hour group level. I'm not following why you think you can't do that. I think it would help if you show some sample data.

-LB

 

[tawild71]

The field dIntervalStart of the IAgentQueueStats table sample values are:

2010-08-28 10:23:000...
2010-08-30 01:04:000...

The system using this database is the Interactive Intelligence Customer Interaction Center. It is a telephony phone system using channel banks, SIP, and Vo/IP.

I did what you told me but I found that there is a custom function in the code created by the software vendor that is causing an error in the vendors report display software. When I take the function out of the code, The report runs without error. I could just use regular functions to display the time in HH:MM. Just courious, is there another way to achieve what I am trying to do with this report?

Thanks

 

[lbass]

Can you confirm that the datatype of the field is in fact datetime? What is the function that you removed? What did it do? I'm unclear on what the problem is. Is it that the format has three digits for seconds? Why are you showing three dots (an ellipse) at the end of each field--are they really part of the field?

-LB

 

[tawild71]

The time format is a little different from what was above. It is actually..

2010-08-28 10:23:40.000
2010-08-30 01:04:34.000

Sorry about that.

The report reads like this now which have a interval of 30 Minutes:

John Doe Avg
Ints Talk
---------------------------------------
DHS Status
8:00:00 AM 1 7

8:30:00 AM 1 300

9:00:00 AM 3 500

9:30:00 AM 5 1000

I need the report in an hourly interval with the seconds being relected for an entire hour.

John Doe Avg
Ints Talk
---------------------------------------
DHS Status
8:00:00 AM 2 307

9:00:00 AM 8 1500

The Interactive Intelligence vendor function takes a Time value and converts it to a string for sorting. The format is HH:MM:SS.

I hope this makes things clearer. Thank you for helping me.

 

[lbass]

I'm not asking about the format for the groupname--I would like to know what the datatype of the field dIntervalStart that results in:

2010-08-28 10:23:40.000
2010-08-30 01:04:34.000

Is this field a datetime or a string?

-LB

 

[tawild71]

The dIntervalStart field in the IAgentQueueStats table is a DateTime field.

 

[lbass]

Then again, all you need to do is replace your TimeInSeconds group with a group that you insert on
{IAgentQueueStats.dIntervalStart} that you set to print on change of hour. You don't need to convert it to a time, since you already have a group on date. Then you can format the groupname to display as a time only by right clicking on it->format field->etc. Or you can replace the groupname (not the group) with your vendor function. Then for your average, just use:

if Sum({AgentQueueStats.nAnsweredAcd},{IAgentQueueStats.dIntervalStart},"by hour") > 0 Then
Sum({AgentQueueStats.tTalkAcd},{IAgentQueueStats.dIntervalStart},"by hour")/
Sum({AgentQueueStats.nAnsweredAcd},IAgentQueueStats.dIntervalStart},"by hour")

-LB