crunch data using perl

Posted on 2004-09-28
Last Modified: 2012-05-05
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?
Question by:TPoly
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 12

Expert Comment

ID: 12167313
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.


LVL 12

Expert Comment

ID: 12167374

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')
LVL 25

Accepted Solution

lwadwell earned 250 total points
ID: 12167523
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')


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question