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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (,  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (…
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now