Grouping by Time Intervals from a formula

[T3leTech]

I am trying to create a report that groups the data by time intervals. However, the time intervals don't exist "naturally" in the database, so I have to create them (some how) with formulas in Crystal Reports. (BTW, I'm using SQL Server 2000, Crystal Reports version 8.0 and Seagate Info version 7.0).

Here is an example of the information I want:
Parameter: StartDateTime and EndDateTime; I want users to be able to select the time period (dates) to run the report on--whether that is one day or across two years.
Display data: Number of Calls, number of abandoned calls and service level.
Display criteria: By every hour of operation in the phone center (8 am to 5 pm). I.E. grouped by 8 am, 9 am, 10 am etc, so that the data appears grouped by the hour, not by the date.

Thanks in advance for any help.

 

[nagornyi]

This is a known problem of reporting on a missing data. The common solution would be to use a base table that would include all the dates (or dates/hours) in the desired interval. Here is, for example, a stored procedure that Adi Cohn suggested long ago for this purpose:

CREATE Procedure GetDates (@StartDate SMALLDATETIME, @EndDate SMALLDATETIME) AS
CREATE TABLE #Dates (D SMALLDATETIME)
WHILE @StartDate <= @EndDate
BEGIN
INSERT #Dates VALUES (@StartDate)
SET @StartDate = DATEADD(dd, 1, @StartDate)
END
SELECT D from #Dates
DROP TABLE

The second step is to outer join this base table with your existing call log table. In this step (with a bit of advanced SQL) you can also calculate the desired numbers of calls of each type in each time span.

 

[T3leTech]

Nagornyi,

Thank you for your quick response. I am confused about your reply though...all of the dates/times are already in the call log table. I don't see how copying them to another table will help. Plus, I'm not sure I'm an advanced SQL code writer.

I am hoping to achieve the desired results and grouping within Crystal Reports.

Thank you for your suggestion.

--Telephony Tech

 

[nagornyi]

OK,
if all the time intervals are present, let's approach from another side.
Let's create several formulas to try.

@date:
Date({TABLE.CALL_DATE_TIME})

@hour:
Hour({TABLE.CALL_DATE_TIME})

@count_calls
Count ({TABLE.CALL_DATE_TIME},{@hour})


@is_abandoned:
if {TABLE.CALL_STATUS}='ABANDONED' then 1 else 0

@count_abandoned_calls:
Sum ({@is_abandoned})

Now, we group data by @date, then by @hour, and in detail section we display @count_calls, @count_abandoned_calls ...

Is this any closer to what you need?

 

[T3leTech]

Yes, this is closer, and it is sort of what I've been trying, except I don't know how to select the formulas as the Group AND make the formulas in them work. I either get all of the time intervals (8 am through 5 pm) for all of the dates in the selection (November 1 through November 24) or I get no data at all.

One of the groups that I tried already is on the date/time field from the database, called [IWrkgrpQueueStats.dIntervalStart], which has the date/time information I need. But the formulas (similar to the ones you describe above) are not working properly, or a better way to state it is that I don't know how to make the formulas do what I want.

The data information is not important on the report, except for the user being able to select the date range. The important thing to display (with the grouping and formulas) is each of the 8 am data within the date range (running total summary).

Does that make any sense?

--Telephony Tech

 

[nagornyi]

OK, here is some additional details..

1) Formula can be created right from the report expert -- when you are in the field tag click the formula button
2) If you don't need grouping by date - that's fine, just select hour formula for grouping.
3) Place {TABLE.CALL_DATE_TIME} field in the detail section.

afteer exiting report expert:

4) Place formulas @count_calls, @count_abandoned_calls etc. into the group header, where @hour formula is.
5) suppress detail section.

Any closer now?