TPoly
asked on
crunch data using perl
hi
i have a qn
I want to crunch some text data.
The data is crunch by year (date format yyyy-mm-dd)
the data i want to crunch is always between 1st jan to 31st dec of a particular year
i want to know how to write the sql statement so that it can compare n
match the data to make sure the "year" is the same.
I also want to know how to calculate and print out each month's data (eg jan, feb and so on)
and i also need to calculate the total of these 12 months.
output required
Month Qin Qout Reject Yield
Jan 100 100 0 100.00%
.
.
.
Dec 90 80 10 88.88%
-------------------------- ---------- ---------- --------
Total 1203 1000 203 83.12%
This is part of the Perl code that i write that does similar function but query by a quarter year instead of a year.
if ($datef =~ m"-11-01" && $datet =~ m"-01-31" ){
#connect to the database
my $dbh = DBI->connect('dbi:mysql:rv si','','', {RaiseError=>1, PrintError=>1});
# Extract the data from database
my $d = $dbh->selectall_arrayref(" select qin, qout, yield, reject FROM yield WHERE date BETWEEN '$datef' AND '$datet'");
print ("Quarterly Yield Report Summary for '$dev' between '$datef' to '$datet'\n\n");
print ("Qin\t", "Qout\t", "Yield\t", "Reject\n");
# Print out all the array catch
foreach my $r(@$d) {
print join("\t", @$r), $/;
}
#print the dotted line
print "-"x60, $/;
#Do the calculation
my $t = $dbh->selectrow_arrayref(" select sum(qin), sum(qout), ((sum(qout)/sum(qin))*100) , sum(reject) FROM yield WHERE date BETWEEN '$datef' AND '$datet'");
print join("\t", @$t), $/;
So anybody can help me with this?
ThankssSSSsss
i have a qn
I want to crunch some text data.
The data is crunch by year (date format yyyy-mm-dd)
the data i want to crunch is always between 1st jan to 31st dec of a particular year
i want to know how to write the sql statement so that it can compare n
match the data to make sure the "year" is the same.
I also want to know how to calculate and print out each month's data (eg jan, feb and so on)
and i also need to calculate the total of these 12 months.
output required
Month Qin Qout Reject Yield
Jan 100 100 0 100.00%
.
.
.
Dec 90 80 10 88.88%
--------------------------
Total 1203 1000 203 83.12%
This is part of the Perl code that i write that does similar function but query by a quarter year instead of a year.
if ($datef =~ m"-11-01" && $datet =~ m"-01-31" ){
#connect to the database
my $dbh = DBI->connect('dbi:mysql:rv
# Extract the data from database
my $d = $dbh->selectall_arrayref("
print ("Quarterly Yield Report Summary for '$dev' between '$datef' to '$datet'\n\n");
print ("Qin\t", "Qout\t", "Yield\t", "Reject\n");
# Print out all the array catch
foreach my $r(@$d) {
print join("\t", @$r), $/;
}
#print the dotted line
print "-"x60, $/;
#Do the calculation
my $t = $dbh->selectrow_arrayref("
print join("\t", @$t), $/;
So anybody can help me with this?
ThankssSSSsss
TPoly,
Here's a quite simple grouping query:
select to_char(order_date,'YYYY') year, to_char(order_date,'MON') month, count(1) num_orders
from orders
group by to_char(order_date,'YYYY') , to_char(order_date,'MON')
Here's a quite simple grouping query:
select to_char(order_date,'YYYY')
from orders
group by to_char(order_date,'YYYY')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can extract the year via SQL (Oracle):
select to_char(some_date,'YYYY') YEAR from my_table
To extract the month as a 3 letter name, use 'MON'. The case you use is reflected in your output:
'MON' => 'JAN'
'Mon' => 'Jan'
'mon' => 'jan'
You don't need BETWEEN, you can use grouping queries.
Cheers!
Stefan