max, avg problem

[rock786143]

Hi,

I have a stored procedure which gives the data as following every day:

+--------------+---------------------+-------+
IPAddress AcctTime count

+--------------+---------------------+-------+
10.10.10.2 2009-03-21 00:00:00 9
10.10.10..2 2009-03-21 00:01:00 4

....................................................................|
10.10.10.4|2009-03-21 23:59:00|10
+--------------+---------------------+-------+

I trying to get the following data from the table
table report for day:
Columns: Max Count/min, Avg count/min, Max Count/hour, Max Hour, Avg Count/hour
Rows: All , Row per IPAddress

How do i get this in a single report?
I can create group on ip and i can get Max Count/min, Avg count/min but how to get next fields.

Please suggest a method. Thanks in advance.

 

[Madawc]

You've not said what you are doing already. If you are getting your answer by grouping by minutes, then have a higher-level grouping using hours.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[CoSpringsGuy]

show your sql formula for the stored procedure

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

 

[rock786143]

Here is my procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `tablereport`(in ReportStarttime datetime)
BEGIN
DECLARE V1 DATETIME;
DECLARE V2 DATETIME;
DECLARE V3 DATETIME;
DECLARE constimeinterval varchar(15);
DECLARE NASIPMIN VARCHAR(15);
DECLARE NASIPMAX VARCHAR(15);
DECLARE NASIPCUR VARCHAR(15);
DECLARE count INT;
set constimeinterval='0:01:0';

set V1=ReportStarttime;
set V3=ADDTIME(V1,'1 0:0:0.000000');
select min(nasipaddress) into nasipmin from radiusacct where AcctStopTime>=ReportStarttime and AcctStopTime<=V3;
select max(nasipaddress) into nasipmax from radiusacct where AcctStopTime>=ReportStarttime and AcctStopTime<=V3;
set nasipcur=nasipmin;

incrementip: LOOP


set V1=ReportStarttime;
set V3=ADDTIME(V1,'1 0:0:0.000000');

incrementstartdate: LOOP
set V2=ADDTIME(V1,constimeinterval);

IF v2 > V3 THEN
LEAVE incrementstartdate;
END IF;

select count(acctsessionid) into count from radiusacct where AcctStopTime >= V1 and AcctStopTime <V2 and nasipaddress=nasipcur;
insert into tablerpt (AcctStopTime,nasipaddress,count) values(V1,nasipcur,count);
set V1=V2;
set V2= ADDTIME(V1,constimeinterval);

END LOOP incrementstartdate;

IF nasipcur >= nasipmax THEN
LEAVE incrementip;
END IF;
select min(nasipaddress) into nasipcur from radiusacct where nasipaddress> nasipmin and AcctStopTime>=ReportStarttime and AcctStopTime<V3;
set nasipmin=nasipcur;
END LOOP incrementip;
END

table structure:
mysql> desc tablerpt;
+--------------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------------------+-------+
| NASIPAddress | varchar(15) | NO | | | |
| AcctStopTime | datetime | NO | | 0000-00-00 00:00:00 | |
| count | bigint(20) | YES | | NULL | |
+--------------+-------------+------+-----+---------------------+-------+
3 rows in set (0.00 sec)

I'm trying get a report in tabular format:
Columns: Max Count/min, Avg count/min, Max Count/hour, Max Hour, Avg Count/hour
Rows: All , Row per IPAddress

Please help me.

 

[Madawc]

You're doing work in the SQL procedure that is better done in Crystal.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[rock786143]

>>You're doing work in the SQL procedure that is better done in Crystal.

Please suggest me how to do this in Crystal. Any sample formula would be appreciated.

 

[Madawc]

Cystal does most of the work for you, once you get used to its ways. I suggest you take a few hours to do a few test reports and learn how it works. It will save time in the long run.

The use of Crystal's automated totals is outlined at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP