[sf-perl] Perl's to_date() equivalent
Loo, Peter # PHX
Peter.Loo at source.wolterskluwer.com
Fri Sep 1 15:13:24 PDT 2006
Hi David,
The problem is that the database is Netezza. Although Netezza will be
coming out with its own internal language, equivalent to, PL/SQL, 4GL,
etc..., it is currently not available.
The logic is quite simple really. First I SELECT the required rows in
the correct sort order. Then I calculate the dates within the records.
The problem is that when I fetch in a row, I have to compare the date of
the current row to a previous row then add days to the date accordingly.
Also there is a column that contains grace period of which I also need
to compare against the difference of the dates.
Here is the logic in pseudo code:
SELECT statement:
=================
select distinct x.ptnt_gid, x.prdct_gid, x.rx_fill_dte,
x.claim_gid, x.ovrd_days_sply_nbr,
x.clfsn_grace_prd_nbr, 'N',
x.rx_fill_dte + x.ovrd_days_sply_nbr
from p_dlvrb_rx_claim_extract x, p_dlvrb_ptnt_list y
where x.dlvrb_gid = y.dlvrb_gid
and x.ptnt_gid = y.ptnt_gid
order by x.ptnt_gid, x.prdct_gid, x.rx_fill_dte, x.claim_gid
WHILE loop:
===========
while (fetch) {
if (previous cted - current rx_fill_dte) >= 0 or
((previous cted - current rx_fill_dte) < 0 and
(previous cted - current rx_fill_dte) <= current
clfsn_grace_prd_nbr)
then
current cted = previous cted + rx_fill_dte
current class = "C"
elsif (previous cted - current rx_fill_dte) < 0 and
(previous cted - current rx_fill_dte) > current
clfsn_grace_prd_nbr
then
current cted = rx_fill_dte + ovrd_days_sply_nbr
current class = "R"
else
current cted = rx_fill_dte + ovrd_days_sply_nbr
current class = "N"
fi
Peter
-----Original Message-----
From: sanfrancisco-pm-bounces+peter.loo=source.wolterskluwer.com at pm.org
[mailto:sanfrancisco-pm-bounces+peter.loo=source.wolterskluwer.com at pm.or
g] On Behalf Of David Fetter
Sent: Friday, September 01, 2006 2:59 PM
To: San Francisco Perl Mongers User Group
Subject: Re: [sf-perl] Perl's to_date() equivalent
On Fri, Sep 01, 2006 at 02:42:00PM -0700, Loo, Peter # PHX wrote:
>
> Hellow there.
>
> Per Andy's recommendation, I went ahead and had DataTime.pm installed
> on our test node. It turned out that the date calculation is taking
> approximately 11 seconds for every 5,000 records. Is there something
> a bit faster? Here is my code:
Are you sure you can't do this with the DBMS's facilities? It seems to
me that Perl's are what you use when you don't have a DBMS date library
available. Also, could you explain in english what the code is supposed
to be doing?
Cheers,
D
More information about the SanFrancisco-pm
mailing list