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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

11 Experts available now in Live!

Get 1:1 Help Now