How to write EncodedQuery correctly

Jieun Jeong · ‎06-21-2022

I'm trying to write an Encoded Query.

The situation that start date and end date are earlier than date A

and the situation that start date and end date are after date B

I would like to write a encoded query that can obtain results except for the above two cases.

ex)

gr.addEncodedQuery( !('start_date<A ^ end_date<A'));
gr.addEncodedQuery( !('start_date>B ^ end_date>B'));

The above query is a query that I wrote as an example. I don't know how to write it.

Does anyone know how to write the right query?

Hitoshi Ozawa · ‎06-22-2022

My bad. Re-read the question,

The total situation is (start_date, end_date <= b) or (start_date, end_date >= A).

Would need to query twice and combine the results.

That is

var resultArray = [];
var gr1 = new GlideRecord('<table name>');
gr1.addQuery('start_date', '<=', B);
gr1.addQuery('end_date', '<=', B);
gr1.query();
while(gr1.next()) {
  resultArray.push(grs1.sys_id.toString());
}
var gr2 = new GlideRecord('<table name>');
gr2.addQuery('start_date', '>=', A);
gr2.addQuery('end_date', '>=', A);
gr2.query();
while(gr1.next()) {
  resultArray.push(grs2.sys_id.toString());
}

View solution in original post

DrewW · ‎06-23-2022

So what you are saying is that you want all of the records where the start date and the end date are between the dates of Date A and Date B

Here is a query that gives you the projects that have a planned start and planned end date that are in the month of June.

end_dateBETWEENjavascript:gs.dateGenerate('2022-06-01','00:00:00')@javascript:gs.dateGenerate('2022-06-30','23:59:59')^start_dateBETWEENjavascript:gs.dateGenerate('2022-06-01','00:00:00')@javascript:gs.dateGenerate('2022-06-30','23:59:59')