Hi, I need to create the following with predicates, and cannot get it to work,

SELECT * FROM viewDoctorRequests v WHERE v.DateTimeRequested between '2007-01-20 14:09:00.000' and '2007-05-23 14:09:00.000' AND v.DoctorInvolvementTypeId = '69274150-6835-468E-8798-84788BB3027C' AND Name IN ( SELECT sdl.Name FROM Request_DoctorLocation srd, DoctorLocation sdl, Request sr WHERE sr.RequestId = srd.RequestId AND sdl.DoctorLocationId = srd.DoctorLocationId AND sr.DateTimeRequested BETWEEN '2007-01-20 14:09:00.000' and '2007-05-23 14:09:00.000' COUNT(sdl.Name) > 5 )

thanks

DateTime start, end;
string typeId;
//assign values to variables

IPredicate between = New FieldBewteenPredicate(DoctorRequests.DateTimeRequested, start, end);

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(RequestDoctorLocationEntity.Relations.DoctorLocationOnDoctorLocationId);
bucket.Relations.Add(RequestDoctorLocationEntity.Relations.RequestOnRequestId);
bucket.PredicateExpression.Add(between);

IGroupByCollection group = new GroupByCollection();
group.HavingClause = new PredicateExpression(DoctorRequests.Name.SetAggregateFunction(AggragetFunction.Count) > 5);

IPredicateExpression filter = new PredicateExpression();
filter.Add(between);
filter.Add(DoctorRequests.DoctorInvolvementTypeId == typeId);
filter.Add(new FieldSetPredicate(DoctorRequests.Name, RequestDoctorLocationFields.Name, SetOperator.In, bucket, 0, null, group));

adapter.FetchEntityView(MyView, filter);

It would look something like this. I wrote this from memory so there may be some overload errors.