[DUG] another set of eyes

[Jeremy Coulter]

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-bounces at delphi.org.nz [mailto:delphi-bounces at 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

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