sql

I have a table
Acct_nbr, Join_date, Close_date
12,            30-Jun-2007     30-Jan-2008


MY QUESTION : I WANT TO CHECK IF THE ACCOUNT HAS CLOSED HIS ACCOUNT BEFORE COMPLETING 1 FISCAL YEAR. IN THE ABOVE SAMPLE DATA THE ACCOUNT 12 CLOSED HIS ACCOUNT BEFORE COMPLETING ONE FISCAL YEAR. HOW DO I THIS. IDEALLY I WANT THE CHECK TO BE AN IF CONDITION THAT I CAN USE IN MY PLSQL CODE.  I ALSO WANT TO HAVE IF CONDITION TO FIND ACCOUNTS THAT HAVE CLOSED THEIR ACCOUNTS AFTER COMPLETING ONE FISCAL YEAR
 for example:
if (account closed his account before one fiscal year)
then &. (dont worrry about this )
end if
if (account did not close his account before one fiscal year)
then &.
end if

Example of fiscal year Fiscal year starts in  01-Jun-2007 and ends in 31/may/2008.
(Fiscal year starts in June and ends in  May always).
The funsction that I wrote to calculate fiscal year is as follows:
CREATE OR REPLACE function fs_yr(alian_dt date) return date is
ayr number(4);
adate date;
begin
--take year component of alian_dt;
--SELECT TO_CHAR(to_DATE('alian_dt', 'MM/DD/YYYY'),'YYYY') into ayr FROM DUAL;
if ( alian_dt is null )
then
   return to_date('31-DEC-5000');
END IF;
select to_char(alian_dt,'yyyy') into ayr from dual;
select to_date('01-jun-'||ayr) into adate from dual;
if ( alian_dt >= adate )
then
   return to_date('01-jun-'||ayr,'dd-mon-yyyy');
else
   return to_date('01-jun-'||(ayr-1),'dd-mon-yyyy');
end if;
end fs_yr;
/
Start Free Trial