I am writing a report that shows who was present on a specific date. The database has a starting date and an ending date for each individual, so what I need is to check to see if the date the user selected falls in between these dates and return anybody who does. I am working on the assumption that doing this in the parameters is the way to go, but I can't quite get the logic right. I've been trying something like this:
AND (
STARTDATE is null) or (bah.assignment_date = :STARTDATE))
AND (ENDDATE is null) or (bah.assignment_end_date = :ENDDATE))
AND (DATE is null) or DATE BETWEEN :STARTDATE AND :ENDDATE))
...and like this:
AND (DATE is null) or DATE BETWEEN bah.assignment_date AND bah.assignment_end_date))
Is doing it in the parameters the way to do this, and if so what am I missing? I wantATE to be the only parameter selected by the user..
AND (
STARTDATE is null) or (bah.assignment_date = :STARTDATE))AND (
ENDDATE is null) or (bah.assignment_end_date = :ENDDATE))AND (
DATE is null) or DATE BETWEEN :STARTDATE AND :ENDDATE))...and like this:
AND (
DATE is null) or DATE BETWEEN bah.assignment_date AND bah.assignment_end_date))Is doing it in the parameters the way to do this, and if so what am I missing? I want
ATE to be the only parameter selected by the user..