WHERE BETWEEN search condition in sproc

jtyson · August 7th, 2003, 12:47 PM

Hi,

I have a stored procedure where I am trying to select items based on a date range. Input parameters from my aspx page are @startDate and @endDate, and I am selecting items from an AssignedHistory table where records have assignedDate and returnedDate fields. If an item is currently assigned, the returnedDate field is NULL; if an item has been returned and is not reassigned both the assignedDate and returnedDate fields will be populated, and if the item had been assigned, returned, and then reassigned there would be two entries (one with both fields populated representing the previous assignment and one with just assignedDate populated representing the current assignment).

I need to return all items assigned during the inputted date range. I am a little confused but I would like to do:

WHERE assignedDate BETWEEN @startDate AND @endDate
AND (here's where I am confused)

If returnedDate IS NOT NULL, where returnedDate NOT BETWEEN @startDate AND @endDate

Can someone help me with the syntax of the second part?

Thanks,

John

David Cameron · August 7th, 2003, 09:22 PM

Code:

WHERE assignedDate BETWEEN @startDate AND @endDate
AND (   returnedDate IS NULL
        OR
        (returnedDate IS NOT NULL AND returnedDate NOT BETWEEN @StartDate AND @endDate)
    )

regards
David Cameron