[DUG] another set of eyes

[Neven MacEwan]

Jeremy

I might be missing something but an "in range" query woult be

ItemStart >= RangeStart AND ItemEnd <= RangeEnd

just as a range overlap would be

ItemStart <= RangeEnd AND ItemEnd >= RangeStart

HTH

Neven
>
> Hi All.
>
> I am the stage with problem that SOMEONE who’s a bit more lateral 
> thinking than me might be able to see the solution.
>
> I have the following data (just a snapshot).
>
> Item Start End
>
> 1 15 Feb 2010 26 Feb 2010
>
> 2 22 Feb 2010 26 Feb 2010
>
> 3 15 Feb 2010 29 Feb 2010
>
> 4 24 Feb 2010 25 Feb 2010
>
> 5 23 Feb 2010 25 Feb 2010
>
> 6 24 Feb 2010 31 Feb 2010
>
> 7 22 Feb 2010 26 Feb 2010
>
> 8 23 Feb 2010 24 Feb 2010
>
> What I am doing is TRYING to find all items that fall between a given 
> date range.
>
> In this case I am trying to find all the items between the 22 Feb and 
> 26 Feb.
>
> i.e. all of the items should be returned in the above snap shot.
>
> I am trying the following where clause (not real field names):-
>
> WHERE (#22 FEB 2010 13.00.00# Between START And END)
>
> AND (#26 FEB 2010 10.00.00# Between START And END)
>
> However, that missed out Items 1,34,5,6 and I think 8.
>
> If I try:
>
> WHERE START >=#22 FEB 2010 13.00.00# And ENDTIME <=#26 FEB 2010 10.00.00#
>
> It misses out 1,3 and 6
>
> I cant go
>
> WHERE START >=#22 FEB 2010 13.00.00# or ENDTIME <=#26 FEB 2010 
> 10.00.00# as that returns to many records.
>
> Can anyone see what I am missing? I don’t mind if I have to do it in 
> separate queries as I can just union them, but I have been looking at 
> this too long and I just cant figure it out, so I thought another set 
> of eyes might help J
>
> Thanks, Jeremy
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject: unsubscribe