Go Premium for a chance to win a PS4. Enter to Win


crunch data using perl

Posted on 2004-09-28
Medium Priority
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 1000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…
On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
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

877 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