How do you formulate a predicate filter to test if a particular date range already exists or is it better to do it using a stored procedure?

My hypothetical table structure:

MyEventTable

Event ID - (int) PK EventName - varchar (50) StartDate - DateTime EndDate - DateTime

(none of the fields above can be null.)

When I insert a record in above table, I want to check that the record I am trying to insert does not overlap (startdate, enddate) with existing records. How do I do this?

I am using llblgen final 2.6 C#, two class, self servicing. The database can be Oracle 11g or SQL Server 2005.

Here is an approximate code:

// setup new event
EventEntity newEvent = new eventEnttiy();
newEvent.IdEvent= xx;
newEvent.Name = "yyyyy";
newEvent.StartDate = someDate;
newEvent.EndDate = someOtherDate;

// DATE EVENT FILTER CHECK
IRelationPredicateBucket filter = new RelationPredicateBucket();

// startDate overlap
filter.PredicateExpression.Add(EventFields.StartDate < newEvent.StartDate
     && EventFields.EndDate > newEvent.StartDate);

// endDate overlap
filter.PredicateExpression.AddWithOr(EventFields.StartDate < newEvent.EndDate
     && EventFields.EndDate > newEvent.EndDate);

// fetch the number of records that overlap
DataAccessAdapter adapter = new DataAccessAdapter();
int numberOfRecordsThatOverlap = Convert.ToInt16(adapter.GetScalar(
     EventFields.IdEvent, filter, AggregateFunction.Count));

if (numberOfRecordsTahtOverlap == 0)
{
     adapter.SaveEntity(newEvent);
}
else
{
     // error, the dates overlap
}

After doing some search on the internet, I think I have a more compact filter to achieve the same results:

The filter can be replaced with:

filter.PredicateExpression.Add(EventFields.StartDate <= newEvent.EndDate && newEvent.StartDate <= EventFields.EndDate > );

Please tell me if above is wrong.

Thanks very much for the prompt reply. But during the last month or so I noticed, I am not notified by email when a response has been posted even though I always select notify me. Can you please check this.

Krish wrote:

After doing some search on the internet, I think I have a more compact filter to achieve the same results:

The filter can be replaced with:

filter.PredicateExpression.Add(EventFields.StartDate <= newEvent.EndDate && newEvent.StartDate <= EventFields.EndDate > );

Please tell me if above is wrong.

Seems nice. However depending on what do you want it may result in false positives. What overlap means in this situation? that is the question? Consider the following

Overlap 1 (the date range is completely inside the range of the new event) Some DB Event StartDate = 01/March/2010 EndDate = 05/March/2010

Your new Event StartDate = 02/March/2010 EndDate = 04/March/2010

Overlap 2 (either startDate or endDate are inside the range of the new event) Some DB Event StartDate = 01/March/2010 EndDate = 05/March/2010

Your new Event StartDate = 20/Feb/2010 EndDate = 04/March/2010

In Overlap2, the filter will pass only in the second part of the &&, as a result in this case, it appears as if there is not overlap, but the StarDate of the DB Event overlaps the date range of the new event. If this is a person schedule, such person will be in troubles between 01/March and 04/March

Just a thought, it may be that Overlap1 is what you want.

Krish wrote:

Thanks very much for the prompt reply. But during the last month or so I noticed, I am not notified by email when a response has been posted even though I always select notify me. Can you please check this.

You are welcome. Please review your spam box, maybe we are there. Also check that you have registered the correct email address in your profile.