Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks!

I am working in a program and having a big problem with a SQL query, one of you might be able to help me, please. My query has to find some users's balance that have their membership expiring in 45 days from today's date and display the amount. After that, I have from the previously found accounts subtract one year from their date to find the current amount due and also display the results. I might need to nest these SQL calls but no clue how to put them together. I am trying to do this with only one SQL statement since after finding it I have to insert these values into another table. My code shows how far I went with it! 

#!usr\bin\perl

use strict;
use CGI qw/:standard/;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Date::Calc qw(Add_Delta_Days Day_of_Week);


print header();

# *********************************************************GET DATE
my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
$mon++;
$year += 1900;

#Let me calculate the date range, now to 45 business days later:::
my (@n_date, $date_from, $date_to);
for my $x (0..45) { # 45 days later

     my ($n_year,$n_mon,$n_mday) = Add_Delta_Days($year,$mon,$mday,$x)
+;

     if (Day_of_Week($n_year,$n_mon,$n_mday) < 6) {

         my $date = "$n_year-$n_mon-$n_mday";

         push @n_date, $date;

     }

}# end  my for...
$date_from = $n_date[0];
$date_to = $n_date[-1];

my $dbh = DBI->connect("DBI:ODBC:myserver",$user, $pass) || print "Con
+nect fail: $!";

my $sql="SELECT ACCOUNT,SDATE, AMOUNT
                FROM MAINTABLE
                WHERE ACCOUNT<> 0 AND SDATE
                BETWEEN '$date_from' AND '$date_to'"; #45 days
my $sth = $dbh->prepare($sql);
$sth->execute() || die $sth->errstr;

[download]


I can find the account due to renewal in 45 days there, but how could I use a nested sql statement to do the one year subtraction from these dates and display the results I am looking for?

Replies are listed 'Best First'.
Re: Nested Statement
by olus (Curate) on Jan 26, 2008 at 00:56 UTC
    I don't seem to fully understand your question, namely if the amount is just the value on the record of 'one year back from current date', and it seems that you have quite an amount of date calculations to be made on the conditions to select your records.
    It doesn't occur to me any way of doing all that on the same query, and cannot do tests right now.

    I would suggest you to do it separately, first get the results for that date range and then, foreach, get the values for a year ago.
    One other approach would be to have those calculations on a stored procedure.

    update
    Though this is not Perl related I found it intriguing and took the challenge to see if I could come up with a solution.
    I manage to get hands on a database and do some tests.
    -- Create test table
create table mmm (
    mid    number,
    acount varchar2(10),
    amount varchar2(10),
    sdate  date
)
/
commit;

-- populate with some records
insert into mmm values(1, 'a', '1', sysdate     );
insert into mmm values(2, 'a', '3', sysdate - 10);
insert into mmm values(3, 'b', '2', sysdate - 1 );
insert into mmm values(5, 'b', '6', sysdate - 11);
insert into mmm values(4, 'c', '3', sysdate - 2 );
insert into mmm values(6, 'c', '9', sysdate - 12);
insert into mmm values(7, 'd', '1', sysdate - 3 );
insert into mmm values(8, 'e', '1', sysdate - 4 );
commit;

-- confirm that the data is there :)
select * from mmm;
MID                    ACOUNT     AMOUNT     SDATE                    
+ 
---------------------- ---------- ---------- -------------------------
+ 
1                      a          1          26-JAN-08                
+ 
2                      a          3          16-JAN-08                
+ 
3                      b          2          25-JAN-08                
+ 
4                      c          3          24-JAN-08                
+ 
5                      b          6          15-JAN-08                
+ 
6                      c          9          14-JAN-08                
+ 
7                      d          1          23-JAN-08                
+ 
8                      e          1          22-JAN-08                
+ 

8 rows selected

-- and now work some magic

select m1.acount, m1.amount, m1.sdate, m2.amount as "amount 10 days ea
+rlier" 
from mmm m1 
left outer join mmm m2 on (m2.acount=m1.acount)
where (m1.sdate >=sysdate-3 and m1.sdate<=sysdate)
and m2.sdate=(m1.sdate - 10);

ACOUNT     AMOUNT     SDATE                     amount 10 days earlier
+ 
---------- ---------- ------------------------- ----------------------
+ 
a          1          26-JAN-08                 3                     
+ 
b          2          25-JAN-08                 6                     
+ 
c          3          24-JAN-08                 9                     
+ 

3 rows selected

-- These tests were ran on an Oracle 10g DB

    [download]
[reply]
[d/l]
Re: Nested Statement
by pc88mxer (Vicar) on Jan 26, 2008 at 17:39 UTC
    Couple of comments:

    First, I'm not sure your code computes the 45-th business day from the current day. Instead, I think it computes the business day which is 45 calendar days from the current day.

    It is clear that from your script that it would be helpful to encapsulate your business date logic into a module. One option is Date::Business which will do what you want with the nextb() method. Actually, I would recommend implementing this in the database itself with a "business day" table. The schema would look something like this: 

    CREATE TABLE BUSINESS_DAY (
    BUSINESS_DAY_ORDINAL   INT PRIMARY KEY,
    CALENDAR_DAY           DATE
);
CREATE TABLE CALENDAR_DAY (
    CALENDAR_DAY           DATE PRIMARY KEY,
    BUSINESS_DAY_ORDINAL   INT,
    NEXT_BUSINESS_DAY_ORDINAL INT
);

    [download]

    BUSINESS_DAY_ORDINAL gives the business day on or before the specified calendar day and NEXT_BUSINESS_DAY_ORDINAL gives the first business day after the specified calendar day. The advantage of doing this is that you can calculate with business days in your SQL rather than relying on a perl script. Additionally, you can implement irregular rules for business days such as holidays. Of course, you can use a perl script to initially populate these tables.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom