I have rewritten your code in a form that I like. I am not sure that it will meet your needs, but it will show you some techniques that you can use.
Then I moved your SQL into a data structure and since you seem to only be using 1 data element for each select I gave them all the name data so that I can simplify the read loop. Look for as data in the SQL.
To extend this you can add information to the SQL data structure and even put a subroutine in it to handle each request.
use strict;
my $db = "MyServer";
#my $user = MyServerStuff::odbc->{ $db } { user };
my $user = 'bob';
#my $pass = MyServerStuff::odbc->{ $db } { pass };
my $pass = 'bob';
my $dbh = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1})
+;
my @sql = (
[
"g_total",
<<SQL,
select distinct main.type,
history.date_forwarded as data,
COUNT(1) as 'PPending'
from main,history
where main.type='1'
and history.date_forwarded
between dateadd( day, -7, getdate()) and getdate()
group by history.date_forwarded, main.type
SQL
], [
"c_g_total",
<<SQL,
select distinct main.type,
history.date_forwarded as data,
COUNT(1) as 'PPending'
from main,history
where main.type!='1'
and history.date_forwarded
between dateadd( day, -7, getdate()) and getdate()
group by history.date_forwarded,main.type
SQL
], [
"total_pending",
<<SQL,
select status_now as data,
COUNT(1) as 'Got Total'
from main
where status_now='submitted' and type='1'
and submitted_date
between dateadd( day, -7, getdate()) and getdate()
group by status_now
SQL
], [
"c_total_pending",
<<SQL,
select status_now, COUNT(1) as 'Got Total'
from main
where status_now='submitted'and type!='1'
and submitted_date
between dateadd( day, -7, getdate()) and getdate()
group by status_now
SQL
], [
"comp_status_now",
<<SQL,
select status_now as data,
COUNT(1) as 'All_Completed'
from main
where status_now='completed'and type='1'
and submitted_date
between dateadd( day, -7, getdate()) and getdate()
group by status_now
SQL
], [
"c_comp_status_now",
<<SQL,
select status_now as data,
COUNT(1) as 'All_Completed'
from main
where status_now='completed'and type!='1'
and submitted_date
between dateadd( day, -7, getdate()) and getdate()
group by status_now";
SQL
], [
"delete_check",
<<SQL,
select delete_check as data,
COUNT(1) as 'Total Deleted'
from main
where delete_check='yes'and type='1'
and submitted_date
between dateadd( day, -7, getdate()) and getdate()
group by delete_check";
SQL
], [
"c_delete_check",
<<SQL,
select delete_check as data,
COUNT(1) as 'Total Deleted'
from main
where delete_check='yes'and type!='1'
and submitted_date
between dateadd( day, -7, getdate()) and getdate()
group by delete_check
SQL
], [
"total_pending_status_now",
<<SQL,
select status_now as data,
COUNT(1) as 'Total Pending'
from main
where status_now='forwarded' and type='1'
and submitted_date
between dateadd( day, -7, getdate()) and getdate()
group by status_now
SQL
], [
"c_total_pending_status_now",
<<SQL,
select status_now as data,
COUNT(1) as 'Total Pending'
from main
where status_now='forwarded' and type!='1'
and submitted_date
between dateadd( day, -7, getdate()) and getdate()
group by status_now
SQL
]
);
my %sth;
my $n = 0;
for my $sql (@sql) {
$sth{$sql[0]} = $dbh->prepare($sql[1]);
}
my %data;
sub cleanup
{
my $ret = shift;
$ret =~ s/\s+$//g;
return $ret;
}
for my $sthk (keys %sth) {
my $sth = $sth{$sthk};
eval {
$sth->execute();
};
if ($@) {
die $sth->errstr;
}
while (my $pointer = $sth->fetchrow_hashref) {
my $data = cleanup($pointer->{'DATA'});
push(@{$data{$sthk}}, $data);
}
$sth->finish;
}
the data again. You can just run the second for loop.