0

Below is my sample data:

enter image description here

I am trying to form a query where I can get records within a date range.

Date Range is start of the week to end of the week. For example I want all records that falls between July 10th - July 16 AND July 24 to July 30.

What I tried is having all the startweek in a Set<Date> setOfStartWeek and endWeek in Set<Date> setOfendWeek. Used below query :

List<Record> recList = [Select Id,Name,StartDate from Record where StartDate >= :setOfStartWeek AND StartDate <= :setOfEndWeek];

Desired Result => return records between July 10th - July 16 i.e records 1,2 AND return records between July 24 to July 30 i.e record 5,6.

The volume of the records is low = 3600 The date range can be dynamic, and not in my control. I can get setOfStartWeekDates that has July 10,24,31.. max it could contain 12 weeks from first element i.e. July 10 - Sept 25.

An approach but not sure if it would work

I will have a Set setOfdates = {July 10,24,31}. A formula field on the record that give me the start week date like July 10, July 24. Can I use in my SQOL- Where startWeekDate In: setOfStartWeeks , however I this doesn't seem to be working for me. Can we not use a set for Date field in where clause ? like how we do it : Id in :setOfIds ?

11

2 Answers 2

0

Logic to determine if records are within multiple non overlapping date ranges can only be done via post processing. It isn't supported in SOQL at arbitrary scale. If you have only two ranges you are looking at, you could use OR syntax. It won't scale up very far though.

3
  • I can have many start date and end date combinations. By post-processing did you mean running a for loop and checking if it falls between any of the dates? I could get the least start data and farthest end date and then run a logic to identify that.
    – SfdcBat
    Jul 20, 2022 at 18:43
  • That doesn't tell you it is within any range though, it could be in a gap between ranges.
    – Adrian Larson
    Jul 20, 2022 at 19:14
  • Another approach I thought is- Use a formula field on record to display the start week date. for example if record startDate is July 20 , the formula field will show July 17. This way all the records will have start week date on them which I could use in my SQOL like Where startWeekDate In: setOfStartWeeks , however I this doesn't seem to be working for me. Can we not use a set for Date field in where clause ? like how we do it : Id in :setOfIds
    – SfdcBat
    Jul 20, 2022 at 19:36
0

I ended up using the formula field to identify the start of the week and used set of dates in where clause like how i described in comments

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .