Time Increment Group Formula

[jbodine]

I work for a call center and need to create a report, that groups records by a time increment parameter entered at run-time. If run the report for the time frame of 8:00 AM to 10:00 AM with a time increment of 30 minutes, I need to tie my result set (which is coming from a stored procedure) to 30 miniute increments withing the 8-10 time frame. I have a formula that gets me the time increment group, for the data I am returning, but the problem is that it is based off the records I am returning.....so if I dont have any records for a give time increment, then I don't get grouping for that time frame.

BeginTime EndTime AgentName InteractionType
=============================================
8:01:00 8:05:00 JohnDoe Call
8:06:00 8:29:00 JohnDoe Email
9:34:00 9:35:00 JohnDoe Call
9:35:00 9:40:00 JohnDoe Email

WHAT I AM GETTING IS THIS
-------------------------
Agent Name: JohnDoe
Time Inc # Customer Interactions
---------------------------------------
8:00 - 8:30 2
9:30 - 10:00 3

WHAT I WANT IS THIS
-------------------------
Agent Name: JohnDoe
Time Inc # Customer Interactions
---------------------------------------
8:00 - 8:30 2
8:30 - 9:00 0
9:00 - 9:30 0
9:30 - 10:00 3

Formula I use currently
-----------------------
// If time incrment is 1440, which is a whole day, use
// Day increments instead of minutes
// Otherwise use minute increments

if {?TimeIncrement} < 1440 then
truncate(datediff('n',date({sp_DefaultProc.BeginTime}),
{sp_DefaultProc.BeginTime})/{?TimeIncrement},0)
else
datediff('d',{?@BDateTime},{sp_DefaultProc.BeginTime})

Thanks in advanced for any help you can offer.

Regards

Jim

 

[kenhamady]

If you want to show the zero groups you have two primary choices:

Create a subreport that looks for records for each group. They group will print even if there are no records.

Or you could create a conditional total for each group by saying something like:

If the time is 8 then 1 else 0

and then doing a sum of each formula to get the total for that group. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com