Using Perl to Crunch Numbers in a SQL Table 2

[FeiLung]

It has been quite some time since I have had to code anything, and I am at a complete loss of where to start. I do have notes from long ago about making a connection to my database. But past that I just don't know where to begin or how to approach this problem.

I need to produce a report based on data in a table on a SQL database. Please note this database is very old and only supports the most rudimentary of SQL statements. That is why I am trying to find a perl solution.

The database name is callc, the table name is call_history. The call_history table contains the following fields

name = 4 digit numerical number unique to each person
call_duration = the call length in seconds
call_type = I or O

I need to produce the following output:
[tt]
Name Total Inbound Total Outbound Total Handled Average Call Length
1431 205 173 378 2:31
2432 192 301 493 2:03
[/tt]

The definitions of the report headings are as follows:

Name = self explanatory
Total Inbound = total number of records where call type is an "I" for that specific name
Total Outbound = total number of records where call type is an "O" for that specific name
Total Handled = the total of Inbound and Outbound for that specific name
Average Call Length = Total Number of calls divided by the total of call_duration (In Minutes) for that specific name

Thank you for putting up with an old man. By no means do I expect someone to code for me, but a slight smack in the face to point me in the right direction is always appreciated.

FL

 

[FeiLung]

I was looking for a solution to this error at run time:

[tt]
Use of uninitialized value in addition (+) at ./sql.pl line 111.
[/tt]

Line 111 is:

my $total = $info{$key}{I} + $info{$key}{O};

My thinking was since there are records where there is no data for example $info{$key}{I} then the string was never created.

FL

 

[KevinADC]

FeiLung,

try this for sanities sake.

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]Data::Dumper[/green][red];[/red]
[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]@AoA[/blue] = [red]([/red][red])[/red][red];[/red]
[olive][b]while[/b][/olive] [red]([/red][blue]@tmp[/blue] = [blue]$dbi[/blue]->[maroon]fetchrow_array[/maroon][red])[/red] [red]{[/red]
   [url=http://perldoc.perl.org/functions/push.html][black][b]push[/b][/black][/url] [blue]@AoA[/blue],[red][[/red][blue]@tmp[/blue][red]][/red][red];[/red]
[red]}[/red]
[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [maroon]Dumper[/maroon][red]([/red]\[blue]@AoA[/blue][red])[/red][red];[/red]

Should help to see what's going on with the data. Post some results of the print out so we can see too.

Core (perl 5.8.8) Modules used :
[ul]
[li]Data:umper - stringified perl data structures, suitable for both printing and eval[/li]
[/ul]






- Kevin, perl coder unexceptional!

 

[travs69]

Yeah.. I didn't that you were using strict..

 

[FeiLung]

This is what I ended up with. It works like a champ.

my $total;
for my $key (keys %info) {
    if (($info{$key}{I}) && ($info{$key}{O})) {
       $total = $info{$key}{I} + $info{$key}{O};
    } elsif ((!$info{$key}{I}) and ($info{$key}{O})) {
       $total = $info{$key}{O};
    } elsif (($info{$key}{I}) and (!$info{$key}{O})) {
       $total = $info{$key}{I};
  }   
  my $spc = int($info{$key}{duration}/$total);
  my $dhrs = int($spc / (60*60));
  my $dmin = int(($spc - $dhrs*60*60)/(60));
  my $dsec = int($spc - ($dhrs*60*60)-($dmin*60));
  my $rsec = sprintf("%02d", $dsec);
  my $avg = "$dmin:$rsec";

  print FH "$key ";

  if (exists $info{$key}{I}) {
      print FH "$info{$key}{I} ";
     } else {
      print FH "0 ";
  }

  if (exists $info{$key}{O}) {
    print FH "$info{$key}{O} ";
     } else {
      print FH "0 ";
  }
  print FH "$avg\n";
}

Thank you for all your help. Stars all around.