Hi Folks,

I've got the following issue in a lot of forms.

I want to book a Holiday X from 14-01 till 18-01 now and want to check if that interferes with any Holidays allready reserved.

Then you've got 4 situations: Holidays who begin before 14-01 and end before 18-01 (I call this: Before/In) Holidays who begin after 14-01 and end before 18-01 (I call this: In) Holidays who begin after 14-01 and end after 18-01 (I call this: In/After) Holidays who begin before 14-01 and end after 18-01 (I call this: Over)

Is there a query to return all records in a prettier way then creating a filter as following:

filter.Add(filterBeforeIn) filter.AddWithOr(filterIn) filter.AddWithOr(InAfter) filter.AddWithOr(Over)

Because I've the feeling this is a very common problem I've the idea i'm now doing some kind of "brute force" method while there's a lot prettier and easier way.

Thanks in advance for any suggestions,

Greets, Gab

BTW: Now I use the code below, it might have a little error, because I havn't tested in throughly, but seems to do the job for now. But so much code

                DateTime startPeriod = Adiso.Abstracts.Convert.DateTimeManipulation.MakeBeginOfDay(this.clStart.SelectedDate);
                DateTime endPeriod = Adiso.Abstracts.Convert.DateTimeManipulation.MakeEndOfDay(this.clEnd.SelectedDate);

                // BeforeIn
                IPredicateExpression filterBeforeIn = new PredicateExpression();
                filterBeforeIn.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Start,ComparisonOperator.LessEqual,startPeriod));
                filterBeforeIn.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Finish,ComparisonOperator.GreaterEqual,startPeriod));
                filterBeforeIn.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Finish,ComparisonOperator.LessEqual,endPeriod));

                // In
                IPredicateExpression filterIn = new PredicateExpression();
                filterIn.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Start,ComparisonOperator.GreaterEqual,startPeriod));
                filterIn.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Finish,ComparisonOperator.LessEqual,endPeriod));

                // InAfter
                IPredicateExpression filterInAfter = new PredicateExpression();
                filterInAfter.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Start,ComparisonOperator.GreaterEqual,startPeriod));
                filterInAfter.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Start,ComparisonOperator.LessEqual,endPeriod));
                filterInAfter.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Finish,ComparisonOperator.GreaterEqual,endPeriod));

                // Over
                IPredicateExpression filterOver = new PredicateExpression();
                filterOver.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Start,ComparisonOperator.LessEqual,startPeriod));
                filterOver.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Finish,ComparisonOperator.GreaterEqual,endPeriod));

                IPredicateExpression filterTotal = new PredicateExpression();
                IPredicateExpression filterDate = new PredicateExpression();
                IPredicateExpression filterEmployee = new PredicateExpression();

                filterDate.Add(filterBeforeIn);
                filterDate.AddWithOr(filterIn);
                filterDate.AddWithOr(filterInAfter);
                filterDate.AddWithOr(filterOver);
                filterEmployee.Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.EmployeeId, ComparisonOperator.Equal,this.luEmployee.SelectedId));

                filterTotal.Add(filterDate);
                filterTotal.Add(filterEmployee);

To find a holiday that part of it lies between 2 dates (14-01 & 18-01)

This can be achieved by only 2 expressions:

WHERE the holiday start date <= 18-01 AND the holiday end date >= 14-01

Since if the start date was > than 18-01 then this holiday will not fall into the checked period, and also if the end date was before 14-01, then also it will not fall into the checked area.

IPredicateExpression filter = new PredicateExpression();
filter .Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Start,ComparisonOperator.LessEqual,endPeriod));
filter .Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Finish,ComparisonOperator.GreaterEqual,endPeriod));

Note: if you still want to use your code which is logically right, you may want to use the "PredicateFactory.Between" to test a date between 2 dates.

I think you miss the point.

I would like to know it a certain period (more than 1 day) gets crossed with another entity already in the database..

Or am I wrong?

Greets, Gab

gabrielk wrote:

I think you miss the point.

I would like to know it a certain period (more than 1 day) gets crossed with another entity already in the database..

your holidays have a start and end date. So with a compare value predicate (a combination) or with Between predicates, you can filter if a given random period of time with a start and an end date has either the start date between a holiday's start-end dates or the end date or both (but that's already caught by the other two).

so 2 filters: 1 for checking if the startdate of your holiday is in any of the stored holiday period's start-end date period and one for the enddate.

I have made a mistake in the code written but not in the logic the correct code should be:

IPredicateExpression filter = new PredicateExpression();
filter .Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Start,ComparisonOperator.LessEqual,endPeriod));
filter .Add(PredicateFactory.CompareValue(PropertyAssignmentFieldIndex.Finish,ComparisonOperator.GreaterEqual,startPeriod));

And To simplify it, your 4 logical OR-ed expressions were as follows, where Start & Finish are the 2 database fields storing holidays start and end dates. And startPeriod & endPeriod are the period you wish to check for an overlapping holiday that part of it lies within.

// BeforeIn

Start <= startPeriod Finish >= startPeriod Finish <= endPeriod

// In

Start >= startPeriod Finish <= endPeriod

// InAfter

Start >= startPeriod Start <= endPeriod Finish >= endPeriod

// Over

Start <= startPeriod Finish >= endPeriod

So it's easier if considered which entities you don't want.

You don't want any entity that falls in the regions BeforeBefore & AfterAfter i.e any entity that will start after the endperiod for sure will be out of the checked period And also any entity that ended before the startperiod will be out of the checked period too.

//NOT WANTED CASE is the:

Start > endPeriod OR Finish < startperiod

Therefore the WANTED CASE is:

NOT (Start > endPeriod OR Finish < startperiod)

Which is: Start <= endPeriod AND Finish >= startPeriod

If you have any entity in the database that satisfies the above expression, it will for sure satisfy one of your 4 regions.

Since in all of your 4 regions, this rule is valid.

This can be easily understood if you spot your 4 regions on a time line drawing.

Note: this is valid provided that Finish > Start and also endPeriod > startPeriod

Thanks for both answers, I guess this is enough info to make the thread solved!

I will try both approaches during the week when I have to deal with that section of the application again!

Thanks again!

Greets, Gab