Searching in a date range with a date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Searching in a date range with a date range

Author

Topic

DrewBurlingame
Starting Member

49 Posts

Posted - 2002-05-13 : 17:26:30

I haven't had to do this before, and I haven't seen it done, so I figured one of you gurus would have an answer for me.

I have an order table with two dates. Create date and Close date.

The user want's to be able to give a start and end date range to find every order that was open during this period. So at least one date from the search date range should fall between the order date range.

What's the most efficient way to do this?

Thanks,
Drew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-13 : 17:49:36

SELECT * FROM OrdersTable
WHERE CreateDate BETWEEN @startDate AND @endDate
OR CloseDate BETWEEN @startDate AND @endDate

If the CloseDate column can be null, use this instead:

SELECT * FROM OrdersTable
WHERE CreateDate BETWEEN @startDate AND @endDate
OR IsNull(CloseDate, @endDate)BETWEEN @startDate AND @endDate

Edited by - robvolk on 05/13/2002 18:05:49

DrewBurlingame
Starting Member

49 Posts

Posted - 2002-05-13 : 19:41:14

Hey Rob,

Thanks for the response.

I had thought of that, but what happens when the CreateDate is before the given criteria and the CloseDate is after the given criteria? This is what's got me stumped. You pretty much have to check for every date between the Create and Close dates.

As I was typing, it occurred to me that a calendar table would probably do the trick.

I could join the order table to the calendar table where calendar date is between order create and close dates, and then filter the results distinctly where the calendar date is between the start and end dates. Does that sound practical? Or are there better methods?

Thanks

Edited by - drewburlingame on 05/13/2002 19:41:55

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-13 : 21:06:12

SELECT * FROM OrdersTable
WHERE @startDate BETWEEN CreateDate AND CloseDate
OR @endDate BETWEEN CreateDate AND CloseDate
OR CreateDate BETWEEN @startDate AND @endDate
OR CloseDate BETWEEN @startDate AND @endDate

See, I MEANT to post this one earlier

I was testing your ability to spot the flaw with the original.

Yeah, I don't believe it either

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources