Oracle SQL question? Fiscal Years, soft deletes and historical data

I have a report query that current looks something like this:

SELECT *
FROM lookuptable, childtable
where lookuptable.id = childtable.fkid
AND lookuptable.begin_date < sysdate
AND (lookuptable.end_date > sysdate or lookuptable.end_date is null)

I would prefer to modify this in the SQL query to check for the following:

The user can look at all child data valid for previous fiscal years even if the current Lookuptable info is invalid. If they have changed the fiscal year (stored in an application variable) then I want to include all records that were once live during the fiscal year they are in.

I can't picture how to do this and am not used to conditional logic in PL/SQL. A link to AskTom or similar is very cool, I couldn't find it doing the searches I tried to do.

thanks!

Don Received on Fri Feb 03 2006 - 15:26:23 CST