Hi guys,

I need to filter on all employees that have been employed for 4, 5 or 6 months (but not less or more). My database stores a StartDate and an EndDate for every employee.

So, what I need to do is find all employees where (EndDate - StartDate) BETWEEN 4 AND 6, or in other words 4 <= DATEDIFF(month, StartDate, EndDate) <= 6.

Unfortunately I have no idea on how to code a corresponding PredicateExpression. Does anybody have an idea and could help me?

I'm working with SelfService and LLBL 2.5.

Thanks in advance Ingmar

Hi, try using DBFunctionCall, see the manual for details.

Hello goose,

thanks for your suggestion. But I still don't see how to use a DBFunctionCall within a PredicateExpression...

Anyway, it's allright. In the meantime I extended my view by a calculated field (so, SQL Server already provides me with a field that contains the month span). Everything else was a piece of cake.

So, I do have a workaround and it's fine.

Thanks again! Ingmar

Hi Ingmar, this is what you should do:

// define the funcion and apply to an entity field (important: use a field that have a type
// of the DBFunction's result, in this case INT).
EntityField fieldDateDiff = OrderFields.OrderId.SetExpression(
    new DbFunctionCall("DATEDIFF(day, {0}, {1})",  new object[] { OrderFields.OrderDate, OrderFields.ShippedDate }) );

// use above field to filter the data, in this case we use a between predicate
IPredicateExpression filter = new PredicateExpression( new FieldBetweenPredicate(fieldDateDiff, 4, 5) );

// fetch orders
orders.GetMulti(filter);

In above example I WANT ALL ORDERS THAT DELAY BETWEEN 4 AND 5 DAYS TO BE SHIPPED. So apply to your scenario and let us know if it work as expected to you

Hi daelmo,

it's you again. You are really ALWAYS an EXCELLENT help! Thank you so much. And your solution is exactly what I was looking for. I'm learning, I'm learning... ;-)

Ingmar