Re: Efficient date range search?

From: Pablo Sanchez <pablo_at_dev.null>
Date: 7 Oct 2002 14:39:26 -0500
Message-ID: <Xns92A08BC9C5E3Apingottpingottbah@209.189.89.243>

Kieran <kieran_at_dunelm.org.uk> wrote in
news:3DA1DF00.7080004_at_dunelm.org.uk:

> Pablo Sanchez wrote:

>> Pablo Sanchez <pablo_at_dev.null> wrote in
>> news:Xns92A07011F6EE7pingottpingottbah_at_209.189.89.243:
>>
>>

>>>Assuming you're using one of the three, the query would be:
>>>
>>>    select count(*) from pet where died <= "date/time value"
>>>

>>
>>
>> Forgot a case... the above only handles when a pet has died and
>> you've stamped the row with the date of their ... expiration.
>> when a pet is still alive, the assumption is that 'died' will be
>> set to NULL and we need that in the predicate:
>>
>> SELECT count(*)
>> FROM pet
>> WHERE died <= <<date/time value>>
>> OR died IS NULL
>>
>> Sorry for any confusion.

> 
> Unfortunately, this will count pets that haven't been born by the
> date in question.
> 
> The obvious query will be:
> 
> select * from pet where my_date > born and (my_date < died or died
> is null) 
> 
> See my other post below for some thoughts on how an RDBMS might
> optimise this.

Perhaps a bad assumption on my part but I was assuming that we would only populate the table with pets that have been born. If my assumption is wrong, then you're totally right, I need to add the 'born' column in the predicate.

Thx!

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com

Received on Mon Oct 07 2002 - 14:39:26 CDT