Calendar Dates for SQL Lookup 1

[FeiLung]

I am going to attempt to create a Perl script to handle calculating reoccurring call statistics for an inbound call center. I am very new at Perl and am hoping to use all of you fine people as a sounding board to make sure I am going down the right path.

Each day a table is created in a SQL Database with that days call information. The table naming convention is "inboundlogMMDDYY".

On the first of every month I plan on running a cron job that will calculate the number of days in the previous month, the month, and the year. In an attempt to gather enough information to populate a SQL statement to grab the data from each days table for the given month and populate it in a new temporary table that will hold the entire month’s data.

Once this new temporary table is populated with the month’s data, I will be calculating statistics like, average hold time, average talk time, and the number of abandoned calls. All the data for such statistics are in the data set.

This calculated information will be saved out to either a text file or HTML formatted file. This file will in turn be emailed out to the correct entities.

I have in the past been able to craft SQL statements in Perl using the Perl|DBI and also send emails from Perl, so those two items don't scare me much.

But creating a loop that will iterate through the number of days in a month and populate that value in to an SQL statement does scare me...


Before anyone asks about doing all this inside of SQL itself, I am using a version of Informix that is at least 15 years old and as such only supports the very basic of SQL statements.

Any insights you fine people might have a greatly appreciated. As always, sample code is next to godliness.

FeiLung

 

[travs69]

I'm not sure what your looking for. A perl script that loops through a month and creates sql statements for you? What part are you unsure of? How to loop through a month or getting that data into a statement?

 

[FeiLung]

Thank you for your response.

1. I am unsure on how to craft the code to tell me how many days are in a month. Example, today is February 2. If I wanted to run the previous months data I need to figure out how many days are in January.

2. I am unsure on how to loop through each day that is in the month to craft the Table name so I can query each table by name. Example, I will need to run the same query on every table that holds January's data, i.e. inboundlog010107, then inboundlog010207, then inboundlog010307, etc.

With those two items out of the way, I beleive I can handle the rest.

Thank you.

FL

 

[MillerH]

Issue #1: Use the Date::Calc CPAN module:

http://search.cpan.org/search?query=Date::Calc

use Date::Calc qw(Days_in_Month);

my $days = Days_in_Month(2007,1); # Returns 31

Issue #1 (Alternative): Don't bother.

Simply cycle through all the days 1-31. Test to see if the specific day exists before trying to use it and keep a counter to know the total after processing.

Issue #2: The only function it helps to know to do this is sprintf.

http://perldoc.perl.org/functions/sprintf.html

my $month = 1;
my $year = 2007;
my $year2digit = substr $year, -2; # Last 2 Digits

my $counter;
for my $day (1..31) {
	my $logname = sprintf("inboundlog%02d%02d%02d", $month, $day, $year2digit);
	# Test if $logname exists.  Else next.
	$counter++;
	# Do whatever you want with $logname
}

 

[FeiLung]

Thanks MillerH!

This is what I ended up with:

#!/usr/bin/perl -w

use Date::Calc qw(Days_in_Month System_Clock Add_Delta_YM);

my ($year,$month,$day, $hour,$min,$sec, $doy,$dow,$dst) = System_Clock([$mgt]);
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);

for my $ReportDays (1..($ReportDIM)) {
    my $logname = sprintf("inboundlog%02d%02d%02d", $ReportMonth, $ReportDays, $ReportY2D);
    # Test if $logname exists.  Else next.
    print "$logname\t";
    # Do whatever you want with $logname
}

Thank you for showing me the path and not just the answer. I learned alot about the Date::Calc module digging through the CPAN docs. Of course if you see any thing wrong with what I have above please let me know.

FL

 

[MillerH]

Of course if you see any thing wrong with what I have above please let me know.

There is one thing I would change with your script. Always include "use strict;" in any script that you make. This will help you prevent about 95% of the syntax errors that you might make while coding. Always, always, always include it.

#!/usr/bin/perl -w

use Date::Calc qw(Days_in_Month System_Clock Add_Delta_YM);

[COLOR=green]use strict;[/color]

my ($year,$month,$day, $hour,$min,$sec, $doy,$dow,$dst) = System_Clock([COLOR=red][$mgt][/color]);
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);

for my $ReportDays (1..($ReportDIM)) {
    my $logname = sprintf("inboundlog%02d%02d%02d", $ReportMonth, $ReportDays, $ReportY2D);
    # Test if $logname exists.  Else next.
    print "$logname\t";
    # Do whatever you want with $logname
}

Doing this will cause the following error to be returned upon running your script:

[CODE execution results]
Global symbol "$mgt" requires explicit package name at cal.pl line 5.
Execution of cal.pl aborted due to compilation errors.
[/CODE]

Looking at line 5, you will see that you have the variable $mgt used there but is never defined. Upon reading the docs for Date::Calc, you will notice that this is simply a copy and paste error and that you should simply be calling "System_Clock()".

This can easily have catch other types of errors, and will the more you program in perl.

Enjoy.