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:rvsi','','', {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
TPolyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
lwadwellConnect With a Mentor Commented:
Hi TPoly,

by the looks of your DBI->connect, you are using a mySQL database.  stefen73 has given good advise, but with mySQL the equivalent function to Oracle's TO_CHAR() is DATE_FORMAT() - it allows the date value to be reformated based on many available, in your case '%b' is the equivalent to Oracle's 'Mon'.  Also in mySQL you have another function called YEAR() which will returns just the year of a date value - this will make you selection criteria easier and select data only for a given year.

So, to get all of the data you want ... you could try the following SQL:

SELECT date_format(date, '%b'), sum(qin), sum(qout), ((sum(qout)/sum(qin))*100), sum(reject)
  FROM yield
 WHERE year(date) = 2003  *** Change this as you need to
 GROUP BY date_format(date, '%b')

0
 
stefan73Commented:
Hi TPoly,
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
0
 
stefan73Commented:
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')
0
All Courses

From novice to tech pro — start learning today.