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

 

[mbrooks]

What SQL database are you using?

M. Brooks

http://mbrooks.info

 

[FeiLung]

Thank you for your response mbrooks. I am using of Informix from the early 90's. I believe it is Informix version 2.

I also do not have any documentation for Informix v 2.X, so even trying to find what exactly is supported is next to impossible.

FL

 

[KevinADC]

might help, might not:

http://www.informixhandbook.com/

- Kevin, perl coder unexceptional!

 

[mbrooks]

Export the data from the Informix database to a tab delimited file.
At this point you can extract the data you want with a perl script.

M. Brooks

http://mbrooks.info

 

[FeiLung]

I do have a Perl DBI that allows me connectivity to the database. I can make a normal select statement to get the data that I need.

My question is after the sql statement. I am looking for some help in going through the data to produce the report that I listed above.

I am sorry if these seems a bit like, "Can you do the work for me" scenario. I am not intending it to be.

FL

 

[travs69]

Is the duration in seconds or minutes:seconds or what?

 

[travs69]

Duh... ignore that post I'll put my glasses on.

 

[KevinADC]

Let's see what perl code you have so far.

- Kevin, perl coder unexceptional!

 

[FeiLung]

Here is what I have.

#!/usr/bin/perl -w
use strict;
use Date::Calc qw(Days_in_Month System_Clock Add_Delta_YM);

my ($year,$month,$day, $hour,$min,$sec, $doy,$dow,$dst) = System_Clock();
my ($ReportYear,$ReportMonth,$ReportDay) = Add_Delta_YM($year,$month,$day, 0, -1);

my $ReportY2D = substr $ReportYear, -2; # Last 2 Digits
my $ReportDIM = Days_in_Month($ReportYear,$ReportMonth);

my $dbh = DBI->connect('task','','','AtomixDBD');
my @results;


# Generate random number for temp table name.
my $random = int(rand(1000));

# Temp Table Creation SQL Statement
my $createtable = "CREATE TABLE temp$random ".
		"( record_id serial not null, ".
		"name char(4), ".
		"call_duration integer, ".
		"call_type char(1); ".
		"CREATE UNIQUE INDEX rec_id ON temp\"$random\" (record_id);";

# Prepare and Exucute Temp Table Build
my $csql = $dbh->prepare($createtable);
my $cc = $csql->execute;

# Query the necessary tables to populate the temp table.
for my $ReportDays (1..($ReportDIM)) {
	my $logname = sprintf("call_history%02d%02d%02d", $ReportMonth, $ReportDays, $ReportY2D);

	my $sqlstring = "INSERT INTO temp$random (name, call_duration, call_type) ".
	                "SELECT name, call_duration, call_type ".
                        "FROM $logname";

	my $psql = $dbh->prepare($sqlstring);
	my $rc = $psql->execute;    
	}

The basic concept is to create a temp table. Load all the data from each table in to the temp table. Run some number crunching. Store the report. Delete the temp table.

I have gotten as far as creating the temp table and getting the data in to it. Now I am looking for help with crunching the numbers and getting them back out in the above mentioned format.

 

[KevinADC]

OK, now how are you getting your data out of the database and storing it in a some perl varibles so you can begin the crunching? Or is that your qestion? How to read the data from the database and store it in some perl variables?

- Kevin, perl coder unexceptional!

 

[FeiLung]

That is exactly it KevinADC. I am at a complete loss as to how to proceed once I have the data all in one place. If you can help direct me on how to read the data from the database so that I can produce the numbers I need that will be fantastic.

FL

 

[KevinADC]

you should be able to find out how in the DBI documentation:

http://search.cpan.org/~timb/DBI-1.53/DBI.pm#Notation_and_Conventions

if you get stuck post some more questons.



- Kevin, perl coder unexceptional!

 

[travs69]

I don't see any reason to even use the temp table.. it doesn't look like it's doing anything for you. Just read your data out of the DB and put it into a hash of hashes and then loop through that and crunch you numbers.

Maybe something like:

name, call_duration, call_type selected from DB

while (@tmp = $dbi->fetchrow_array) {
  	#increatement I or 0 by 1
	$info{$tmp[0]}{$tmp[1]}++;
	
	#
	$info{$tmp[0]}{'duration'} += $tmp[2];
}

then you can loop back through

for $key (sort keys %info) {
	#I'm just guessing on the call_type you might want to do check that
	$total = $info{$key}{I} + $info{$key}{O};
	$avg = $info{duration}/$total;
	
	print "$key $info{$key}{I} $info{$key}{O} $avg\n";
}

This has not been checked/tested at all.. it is just something to guide you in a direction.

 

[FeiLung]

travs69,

Would you be so kind as to explain your code a little. Hashes are very new to me and I am a little confused by your example.

 

[FeiLung]

Just re-reading your post you also mentioned a hash of hashes. I have done a little reading with the help of google and I have to say I am completely confused now. I understand the concept but can't seem to find any examples that actually explain what is happening. Just code snippets.

 

[FeiLung]

I was able to use the following to make sure that the hash contains the correct information:

for my $keys ( keys %info ) {
    print "$keys: ";
    for my $value ( keys %{ $info{$keys} } ) {
        print "$value=$info{$keys}{$value} ";
    }
    print "\n";
}

But one problem became apparant. Some records do not have any Inbound or Outbound totals. If I use the following code:

for $key (sort keys %info) {
    #I'm just guessing on the call_type you might want to do check that
    $total = $info{$key}{I} + $info{$key}{O};
    $avg = $info{duration}/$total;
    
    print "$key $info{$key}{I} $info{$key}{O} $avg\n";
}

It will spit out a ton of errors due to either O or I not having a quantity. Can't very well add or divide something that is not there. Any suggestions on how to account for that?

 

[travs69]

Just do
if ($avg != 0 ) { $avg = $info{duration}/$total;}else {$avg = 0; }

Once again.. off the top of my head.. no testing or anything

 

[FeiLung]

It appears that comparing them assignments to 0 does not work. I beleive perl is having a problem with it because if there is no data for $info{$key}{I} or $info{$key}{O} then the strings are never created. So we are trying to evaluate a string that does not exist.

if (($info{$key}{I}) && ($info{$key}{O})) {
   my $total = $info{$key}{I} + $info{$key}{O};
   } elsif ((!$info{$key}{I}) and ($info{$key}{O})) {
   my $total = $info{$key}{O};
   } elsif (($info{$key}{I}) and (!$info{$key}{O})) {
   my $total = $info{$key}{I};
   }

I was using the above code to straighten out the $total string. But if $info{$key}{I} or $info{$key}{O} does not exist, then we have issues.

FL

 

[travs69]

I'm not sure why you are doing that. If they don't exist perl just assigns them a value of 0 if you treat them as a numerical. So no need to check if they exist.


I gave you the code wrong anyway:
if ($total != 0 ) { $avg = $info{duration}/$total;}else {$avg = 0; }


Where's KevinADC at to double check my work anyway???