SQL script in Arcade

[AlinaTarmu_SCGIS]

I have a feature set of around 80000 records. One field, called 'equipment', has non-unique values associated with multiple date values from a "date created" field. I have been trying to write an Arcade data expression that basically groups the records by 'equipment' with the dates sorted ascending, uses an SQL script to compare the date values associated with each equipment and filters only the equipment records that have consecutive dates with a difference larger than 2592000 seconds (30 days). My knowledge of SQL scripting is not advanced enough, but I definitely don't want to use "for loops" in this case.

[AlinaTarmu_SCGIS · Accepted solution]

In the end I used SQL filters to reduce the records to only a couple of hundreds and used "for loops" on those. Using DateDiff with months made the most sense for what I needed. The script might not be the most elegant, let me know if there is a simpler way to do this:

var pairs = []

for (var r in rec) {
    var keys = r["Equipment"];
    var values = r["Date"];
    var keyvalue = {"key": keys, "value": values}
    Push(pairs, keyvalue)
}

var resultArray = [];

for (var i = 0; i < Count(pairs)-1; i++) {
  var current = pairs[i];
  var next = pairs[i + 1];

  if (Equals(current.key, next.key) && Abs(DateDiff(Date(current.value),Date(next.value), "months")) <= 1) {
    Push(resultArray, current.key);
    }
}
resultArray

[AlinaTarmu_SCGIS · Accepted solution]

After that I had to add single quotes to the strings in the array, because the final SQL filter would not recognize the strings without quotes in order to match them to the feature set. 

var newFilter = [];
for (var i = 0; i< Count(resultArray)-1; i++) {
    var string = "'" + resultArray[i] + "'";
    newFilter[i] = string
    }
var sqlFilter = `EQUIPMENT IN (${Concatenate(newFilter, ',')})`