last 12 months query for custom records

[Hafsa1]

Hi

I have a custom table(x_app_cost_service) having several records with "period" in format "FY25: M09". attached scrrenshot.

I want to get all records for last 12 months.

suppose today is FY26:M02  (feb month 2026 ), it should calculate 12 months from "FY26:M01" (jan 2026 ) back wards.

Situation happens where some months data not available it should take next month/year data. There could be multiple records for same month. What will be the query to fetch. Because once I get the query I need to calculate the "cost_amount" field for all records. Any idea?

[Juhi Poddar · Accepted solution]

Hi @Hafsa1 

Try this: 

var table = "x_app_cost_service";

// STEP 1: Get last 12 DISTINCT available periods
var gr = new GlideRecord(table);
gr.addNotNullQuery("period");
gr.orderByDesc("period");   // works because format is consistent
gr.query();

var distinctPeriods = [];
var periodMap = {};

while (gr.next()) {

    var period = gr.getValue("period");

    if (!periodMap[period]) {
        periodMap[period] = true;
        distinctPeriods.push(period);
    }

    if (distinctPeriods.length == 12)
        break;
}

// STEP 2: Fetch all records for those 12 periods
var totalCost = 0;

if (distinctPeriods.length > 0) {

    var gr2 = new GlideRecord(table);
    gr2.addQuery("period", "IN", distinctPeriods.join(","));
    gr2.query();

    while (gr2.next()) {
        totalCost += parseFloat(gr2.getValue("cost_amount") || 0); //update the cost_amount field name
    }
}

gs.info("Periods considered: " + distinctPeriods.join(", "));
gs.info("Total cost: " + totalCost);

Hope this helps!

"If you found my answer helpful, please like and mark it as an "accepted solution". It helps future readers to locate the solution easily and supports the community!"

Thank You
Juhi Poddar