------=_Part_130923_13226170.1169152960244
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I know this is not really a Perl question, Except that I use perl yo
generate the page.

I have a report that is displayed on a webpage in an 10 x 9 table

Originally we did it as one query per cell, then we combined it into one
monolithic query that returns one row with 72 columns

I am sure that there is some way to optimize this, maybe using sub-query,
which I have not been able to grasp yet.

so here is the beast:

my ($newdata, $noanswerdata, $timeddata, $misseddata, $highdata,
$normaldata, $lowdata, $monitordata);

# Open DB
my $dbh = DBI->connect( "dbi:$datasrc", "$sqluser", "$sqlpassword") ||
error("Unable to open user database: $DBI::errstr");

# Setup the Query
my $query = qq{SELECT
# New
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
# No answer
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
noanswer='1'),
# Timed Callback
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND NOT
date_format(timedcallback, "%Y")='0000'),
# Missed Timed Callbacks
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
# High Priority
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
priority='1'),
# Normal Priority
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
priority='2'),
# Low Priority
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
priority='3'),
# Monitor
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='M')
FROM ticketsys
WHERE project=?
AND assignedto='POOL'
GROUP BY project};
my $sth = $dbh->prepare($query);
$sth->execute($project) || error("Cannot execute: $DBI::errstr");
my ($newolder, $newtodayminus6, $newtodayminus5, $newtodayminus4,
$newtodayminus3, $newtodayminus2, $newtodayminus1, $newtoday, $naolder,
$natodayminus6, $natodayminus5, $natodayminus4, $natodayminus3,
$natodayminus2, $natodayminus1, $natoday, $tcolder, $tctodayminus6,
$tctodayminus5, $tctodayminus4, $tctodayminus3, $tctodayminus2,
$tctodayminus1, $tctoday, $mcolder, $mctodayminus6, $mctodayminus5,
$mctodayminus4, $mctodayminus3, $mctodayminus2, $mctodayminus1, $mctoday,
$hpolder, $hptodayminus6, $hptodayminus5, $hptodayminus4, $hptodayminus3,
$hptodayminus2, $hptodayminus1, $hptoday, $npolder, $nptodayminus6,
$nptodayminus5, $nptodayminus4, $nptodayminus3, $nptodayminus2,
$nptodayminus1, $nptoday, $lpolder, $lptodayminus6, $lptodayminus5,
$lptodayminus4, $lptodayminus3, $lptodayminus2, $lptodayminus1, $lptoday,
$monolder, $montodayminus6, $montodayminus5, $montodayminus4,
$montodayminus3, $montodayminus2, $montodayminus1, $montoday);
$sth->bind_columns( undef, \$newolder, \$newtodayminus6,
\$newtodayminus5, \$newtodayminus4, \$newtodayminus3, \$newtodayminus2,
\$newtodayminus1, \$newtoday, \$naolder, \$natodayminus6, \$natodayminus5,
\$natodayminus4, \$natodayminus3, \$natodayminus2, \$natodayminus1,
\$natoday, \$tcolder, \$tctodayminus6, \$tctodayminus5, \$tctodayminus4,
\$tctodayminus3, \$tctodayminus2, \$tctodayminus1, \$tctoday, \$mcolder,
\$mctodayminus6, \$mctodayminus5, \$mctodayminus4, \$mctodayminus3,
\$mctodayminus2, \$mctodayminus1, \$mctoday, \$hpolder, \$hptodayminus6,
\$hptodayminus5, \$hptodayminus4, \$hptodayminus3, \$hptodayminus2,
\$hptodayminus1, \$hptoday, \$npolder, \$nptodayminus6, \$nptodayminus5,
\$nptodayminus4, \$nptodayminus3, \$nptodayminus2, \$nptodayminus1,
\$nptoday, \$lpolder, \$lptodayminus6, \$lptodayminus5, \$lptodayminus4,
\$lptodayminus3, \$lptodayminus2, \$lptodayminus1, \$lptoday, \$monolder,
\$montodayminus6, \$montodayminus5, \$montodayminus4, \$montodayminus3,
\$montodayminus2, \$montodayminus1, \$montoday);
$sth->fetch();
$sth->finish();

Any help in taming the monster would be nice.

Randy B.

------=_Part_130923_13226170.1169152960244--

Marginally offtopic, but there is an upcoming free "webinar" being offere=
d by MySQL on query execution:

http://www.mysql.com/news-and-events/web-seminars/query-exec ution.php

Might be worthwhile attending.

-----Original Message-----
From: Randy Burke [mailto:uncle.ranny [at] gmail.com]
Sent: Thursday, January 18, 2007 2:43 PM
To: perl [at] lists.mysql.com
Subject: optimizing a query

I know this is not really a Perl question, Except that I use perl yo gene=
rate the page.

I have a report that is displayed on a webpage in an 10 x 9 table

Originally we did it as one query per cell, then we combined it into one =
monolithic query that returns one row with 72 columns

I am sure that there is some way to optimize this, maybe using sub-query,=
which I have not been able to grasp yet.

so here is the beast:

my ($newdata, $noanswerdata, $timeddata, $misseddata, $highdata, $nor=
maldata, $lowdata, $monitordata);

# Open DB
my $dbh =3D DBI->connect( "dbi:$datasrc", "$sqluser", "$sqlpassword")=
|| error("Unable to open user database: $DBI::errstr");

# Setup the Query
my $query =3D qq{SELECT
# New
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND=
status=3D'O' AND date_format(lastcontact, "%Y")=3D'0000' AND noanswer=3D=
'0'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 6 DAY) A=
ND status=3D'O' AND date_format(lastcontact, "%Y")=3D'0000' AND noanswer=3D=
'0'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 5 DAY) A=
ND status=3D'O' AND date_format(lastcontact, "%Y")=3D'0000' AND noanswer=3D=
'0'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 4 DAY) A=
ND status=3D'O' AND date_format(lastcontact, "%Y")=3D'0000' AND noanswer=3D=
'0'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 3 DAY) A=
ND status=3D'O' AND date_format(lastcontact, "%Y")=3D'0000' AND noanswer=3D=
'0'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 2 DAY) A=
ND status=3D'O' AND date_format(lastcontact, "%Y")=3D'0000' AND noanswer=3D=
'0'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 1 DAY) A=
ND status=3D'O' AND date_format(lastcontact, "%Y")=3D'0000' AND noanswer=3D=
'0'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() ) AND status=3D'O' =
AND date_format(lastcontact, "%Y")=3D'0000' AND noanswer=3D'0'),
# No answer
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND=
status=3D'O' AND noanswer=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 6 DAY) A=
ND status=3D'O' AND noanswer=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 5 DAY) A=
ND status=3D'O' AND noanswer=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 4 DAY) A=
ND status=3D'O' AND noanswer=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 3 DAY) A=
ND status=3D'O' AND noanswer=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 2 DAY) A=
ND status=3D'O' AND noanswer=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 1 DAY) A=
ND status=3D'O' AND noanswer=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() ) AND status=3D'O' =
AND noanswer=3D'1'),
# Timed Callback
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND=
status=3D'O' AND NOT date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 6 DAY) A=
ND status=3D'O' AND NOT date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 5 DAY) A=
ND status=3D'O' AND NOT date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 4 DAY) A=
ND status=3D'O' AND NOT date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 3 DAY) A=
ND status=3D'O' AND NOT date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 2 DAY) A=
ND status=3D'O' AND NOT date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 1 DAY) A=
ND status=3D'O' AND NOT date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() ) AND status=3D'O' =
AND NOT date_format(timedcallback, "%Y")=3D'0000'),
# Missed Timed Callbacks
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND=
status=3D'O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT da=
te_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 6 DAY) A=
ND status=3D'O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT =
date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 5 DAY) A=
ND status=3D'O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT =
date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 4 DAY) A=
ND status=3D'O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT =
date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 3 DAY) A=
ND status=3D'O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT =
date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 2 DAY) A=
ND status=3D'O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT =
date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 1 DAY) A=
ND status=3D'O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT =
date_format(timedcallback, "%Y")=3D'0000'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() ) AND status=3D'O' =
AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT date_format(time=
dcallback, "%Y")=3D'0000'),
# High Priority
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND=
status=3D'O' AND priority=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 6 DAY) A=
ND status=3D'O' AND priority=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 5 DAY) A=
ND status=3D'O' AND priority=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 4 DAY) A=
ND status=3D'O' AND priority=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 3 DAY) A=
ND status=3D'O' AND priority=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 2 DAY) A=
ND status=3D'O' AND priority=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 1 DAY) A=
ND status=3D'O' AND priority=3D'1'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() ) AND status=3D'O' =
AND priority=3D'1'),
# Normal Priority
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND=
status=3D'O' AND priority=3D'2'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 6 DAY) A=
ND status=3D'O' AND priority=3D'2'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 5 DAY) A=
ND status=3D'O' AND priority=3D'2'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 4 DAY) A=
ND status=3D'O' AND priority=3D'2'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 3 DAY) A=
ND status=3D'O' AND priority=3D'2'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 2 DAY) A=
ND status=3D'O' AND priority=3D'2'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 1 DAY) A=
ND status=3D'O' AND priority=3D'2'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() ) AND status=3D'O' =
AND priority=3D'2'),
# Low Priority
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND=
status=3D'O' AND priority=3D'3'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 6 DAY) A=
ND status=3D'O' AND priority=3D'3'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 5 DAY) A=
ND status=3D'O' AND priority=3D'3'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 4 DAY) A=
ND status=3D'O' AND priority=3D'3'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 3 DAY) A=
ND status=3D'O' AND priority=3D'3'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 2 DAY) A=
ND status=3D'O' AND priority=3D'3'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 1 DAY) A=
ND status=3D'O' AND priority=3D'3'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() ) AND status=3D'O' =
AND priority=3D'3'),
# Monitor
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND=
status=3D'M'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 6 DAY) A=
ND status=3D'M'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 5 DAY) A=
ND status=3D'M'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 4 DAY) A=
ND status=3D'M'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 3 DAY) A=
ND status=3D'M'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 2 DAY) A=
ND status=3D'M'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() - INTERVAL 1 DAY) A=
ND status=3D'M'),
sum(TO_DAYS(creationdate)=3DTO_DAYS(now() ) AND status=3D'M')
FROM ticketsys
WHERE project=3D?
AND assignedto=3D'POOL'
GROUP BY project};
my $sth =3D $dbh->prepare($query);
$sth->execute($project) || error("Cannot execute: $DBI::errstr");
my ($newolder, $newtodayminus6, $newtodayminus5, $newtodayminus4, $ne=
wtodayminus3, $newtodayminus2, $newtodayminus1, $newtoday, $naolder, $nat=
odayminus6, $natodayminus5, $natodayminus4, $natodayminus3, $natodayminus=
2, $natodayminus1, $natoday, $tcolder, $tctodayminus6, $tctodayminus5, $t=
ctodayminus4, $tctodayminus3, $tctodayminus2, $tctodayminus1, $tctoday, $=
mcolder, $mctodayminus6, $mctodayminus5, $mctodayminus4, $mctodayminus3, =
$mctodayminus2, $mctodayminus1, $mctoday, $hpolder, $hptodayminus6, $hpto=
dayminus5, $hptodayminus4, $hptodayminus3, $hptodayminus2, $hptodayminus1=
, $hptoday, $npolder, $nptodayminus6, $nptodayminus5, $nptodayminus4, $np=
todayminus3, $nptodayminus2, $nptodayminus1, $nptoday, $lpolder, $lptoday=
minus6, $lptodayminus5, $lptodayminus4, $lptodayminus3, $lptodayminus2, $=
lptodayminus1, $lptoday, $monolder, $montodayminus6, $montodayminus5, $mo=
ntodayminus4, $montodayminus3, $montodayminus2, $montodayminus1, $montoda=
y);
$sth->bind_columns( undef, \$newolder, \$newtodayminus6, \$newtodaymi=
nus5, \$newtodayminus4, \$newtodayminus3, \$newtodayminus2, \$newtodaymin=
us1, \$newtoday, \$naolder, \$natodayminus6, \$natodayminus5, \$natodaymi=
nus4, \$natodayminus3, \$natodayminus2, \$natodayminus1, \$natoday, \$tco=
lder, \$tctodayminus6, \$tctodayminus5, \$tctodayminus4, \$tctodayminus3,=
\$tctodayminus2, \$tctodayminus1, \$tctoday, \$mcolder, \$mctodayminus6,=
\$mctodayminus5, \$mctodayminus4, \$mctodayminus3, \$mctodayminus2, \$mc=
todayminus1, \$mctoday, \$hpolder, \$hptodayminus6, \$hptodayminus5, \$hp=
todayminus4, \$hptodayminus3, \$hptodayminus2, \$hptodayminus1, \$hptoday=
, \$npolder, \$nptodayminus6, \$nptodayminus5, \$nptodayminus4, \$nptoday=
minus3, \$nptodayminus2, \$nptodayminus1, \$nptoday, \$lpolder, \$lptoday=
minus6, \$lptodayminus5, \$lptodayminus4, \$lptodayminus3, \$lptodayminus=
2, \$lptodayminus1, \$lptoday, \$monolder, \$montodayminus6, \$montodaymi=
nus5, \$montodayminus4, \$montodayminus3, \$montodayminus2, \$montodaymin=
us1, \$montoday);
$sth->fetch();
$sth->finish();

Any help in taming the monster would be nice.

Randy B.


INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR T=
HE PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE=
=2E If you are not an intended recipient of this message, or an agent res=
ponsible for delivering it to an intended recipient, you are hereby notif=
ied that you have received this message in error, and that any review, di=
ssemination, distribution, or copying of this message is strictly prohibi=
ted. If you received this message in error, please notify the sender imme=
diately, delete the message, and return any hard copy print-outs.=0A=0ATh=
is message has been scanned for viruses by McAfee's Groupshield.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules [at] m.gmane.org