It is absolutely possible.
I changed your example table to reflect that the counts are not taken daily. I used the same values for depots A and B:
For the production, I just assumed that depot A fills 10 sandbags per day, while depot B manages to fill 20.
// load the count fs
var count_fs = Featureset(Text({
fields: [
{name: "DateOfCount", type: "esriFieldTypeDate"},
{name: "Depot", type: "esriFieldTypeString"},
{name: "Count", type: "esriFieldTypeInteger"},
],
features: [
{attributes: {DateOfCount: Number(Date(2022,11,1)), Depot: "A", Count: 100}},
{attributes: {DateOfCount: Number(Date(2022,11,6)), Depot: "A", Count: 50}},
{attributes: {DateOfCount: Number(Date(2022,11,22)), Depot: "A", Count: 150}},
{attributes: {DateOfCount: Number(Date(2022,11,1)), Depot: "B", Count: 100}},
{attributes: {DateOfCount: Number(Date(2022,11,6)), Depot: "B", Count: 50}},
{attributes: {DateOfCount: Number(Date(2022,11,22)), Depot: "B", Count: 150}},
],
geometryType: ""
}))
//return count_fs
// load the production fs
var production_fs = {
fields: [
{name: "DateOfProduction", type: "esriFieldTypeDate"},
{name: "Depot", type: "esriFieldTypeString"},
{name: "Count", type: "esriFieldTypeInteger"},
],
features: [],
geometryType: ""
}
for(var i = 0; i < 30; i++) {
Push(production_fs.features, {attributes: {DateOfProduction: Number(DateAdd(Date(2022,11,1), i, "days")), Depot: "A", Count: 10}})
Push(production_fs.features, {attributes: {DateOfProduction: Number(DateAdd(Date(2022,11,1), i, "days")), Depot: "B", Count: 20}})
}
production_fs = Featureset(Text(production_fs))
//return production_fs
// create the output dict
var out = {
fields: [
{name: "DateOfCount", type: "esriFieldTypeDate"},
{name: "Depot", type: "esriFieldTypeString"},
{name: "Count", type: "esriFieldTypeInteger"},
{name: "Taken", type: "esriFieldTypeInteger"},
{name: "Produced", type: "esriFieldTypeInteger"},
],
features: [],
geometryType: ""
}
// loop over depots
var depots = ["A", "B"]
for(var d in depots) {
// find all counts of the depot and order by date
var depot = depots[d]
var depot_count_fs = Filter(count_fs, "Depot = @depot")
depot_count_fs = OrderBy(depot_count_fs, "DateOfCount")
// loop over depot_count_fs
var previous_depot_count = First(depot_count_fs)
for(var depot_count in depot_count_fs) {
// find all entries in the production_fs between now and the previous count date
var start_date = previous_depot_count.DateOfCount
var end_date = depot_count.DateOfCount
var production_between_dates = Filter(production_fs, "Depot = @depot AND DateOfProduction > @start_date AND DateOfProduction <= @end_date")
// calculate the values
var current_count = depot_count.Count
var produced = Sum(production_between_dates, "Count")
var taken = previous_depot_count.Count + produced - current_count
// append new feature
Push(out.features, {attributes: {DateOfCount: Number(depot_count.DateOfCount), Depot: depot, Count: current_count, Taken: taken, Produced: produced}})
// set previous
previous_depot_count = depot_count
}
}
return Featureset(Text(out))
The "Taken" and "Produced" columns list the sandbags that were taken and produced between the current and the previous count.
Lines 1-35 define the sample data. They should of course be replaced with code loading your actual data sets.
Depending on your database and date format, you might have to change the sql clause in line 64.
Have a great day!
Johannes