[DUG] another set of eyes

[Gary T. Benner]

[Reply]

HI Jeremy,

First off, what are you really wanting? .... from what I can see  the query is asking is if any of the content between START and END lies within the specified range ....22/2 - > 26/2.

Viewing it pictorially ..  x denotes the START END range


	20	21	22	23	24	25	26	27

	x	x							fail
	
	x	x	x	x					accept
	
				x	x	x			accept
							
					x	x	x	x	accept
									
								x	fail
															
															

here goes:

select ...
where (  END>=#22 FEB 2010 00.00.00#  and END <=#26 FEB 2010 00.00.00# )
or (  START>=#22 FEB 2010 00.00.00# and START<=#26 FEB 2010 00.00.00#  )

Note this is untested, but should get you in the right direction.

HTH

Gary



At 09:52 on 31/01/2010 you wrote 
>To  : delphi at delphi.org.nz
>CC  : >From: Jeremy Coulter, jscoulter at gmail.com
>Content Type: text/html
>Attached: >
>This is a multipart message in MIME format.
>
>
>
>
>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

Ref#: 41006

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