[DUG] another set of eyes - correction

[Jeremy Coulter]

I shall give that a try and get back to you.

You know how it is, look at something long enough and you eventually loose
site of what you are trying to do ....aghhh......

 

Jeremy

 

From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
Behalf Of Xander (GMail)
Sent: Sunday, 31 January 2010 10:47
To: 'NZ Borland Developers Group - Delphi List'
Subject: Re: [DUG] another set of eyes - correction

 

Sorry, correction (on last line):

 

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) 

 

 

  _____  

From: Xander (GMail) [mailto:xandervdm at gmail.com] 
Sent: Sunday, January 31, 2010 10:45 AM
To: 'NZ Borland Developers Group - Delphi List'
Subject: RE: [DUG] another set of eyes

 

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 J

 

Thanks, Jeremy

  

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