Thanks Nicholas.
Have found one small flaw though. There is one item that has a start date of the 31st Jan and an end date of the 14th March that does not get picked up. I am sure its just a tinker of the where clause. Jeremy From: delphi-boun...@delphi.org.nz [mailto:delphi-boun...@delphi.org.nz] On Behalf Of Nicholas Barrett Sent: Sunday, 31 January 2010 11:21 To: delphi Subject: Re: [DUG] another set of eyes Hi Jeremy, I'm not sure this solution has been given yet but this is what we use all the time WHERE (START_FIELD < END_RANGE) AND (END_FIELD > START_RANGE) This will select all records who's START_FIELD and END_FIELD somehow intersect the START_RANGE to END_RANGE. There are six cases that we are accounting for. Four we want and two we don't. SF < SR and EF < SR = FAIL SF < SR and EF > SR = PASS SF > SR and EF < ER = PASS SF < ER and EF > ER = PASS SF > ER and EF > ER = FAIL SF < SR and EF > ER = PASS The two fails are when the data does not intersect at all, the rest all pass. Hope that helps. Cheers, Nick Barrett
_______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe
