[DUG] another set of eyes

[Xander (GMail)]

Hi Jeremy,

 

If I understand your problem correctly, the following should work:

 

WHERE 

                -- those that begin inside the range period:

(START >= RangeStart AND START <= RangeEnd) OR

                -- those that end inside the range period:

 (END >= RangeStart AND END <= RangeEnd) OR

-- those that begin before and end after the range period:

(START <= RangeStart AND END <  RangeEnd) 

 

 

Cheers

  _____  

From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
Behalf Of Jeremy Coulter
Sent: Sunday, January 31, 2010 9:54 AM
To: 'NZ Borland Developers Group - Delphi List'
Subject: [DUG] another set of eyes

 

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 :-)

 

Thanks, Jeremy

  

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20100131/db0bc1d2/attachment.html