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?

In reply to Nested Statement by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.