Script to count RITM

[tsoct]

Hello, everyone.
I need to count the number of ritms every day depending on the actual end and actual start field.

If:

1) RITM123456's Actual Start is July 9, 2024, and its Actual End is July 11, 2024,

2) RITM789101's Actual Start is July 9, 2024, and its Actual End is July 9, 2024,

the script count should return:
09 July count = 2.
10 July count = 1.
11 July count = 1.

How can this be accomplished using script?

[Yashsvi]

Hi @tsoct,

please try below script:

// Define the start and end date range (modify as needed)
var startDate = gs.dateGenerate('2024-07-09'); // Start date
var endDate = gs.dateGenerate('2024-07-11');   // End date

// Initialize an empty object to store counts per date
var countPerDate = {};

// Query RITMs within the date range
var ritmGR = new GlideRecord('sc_req_item');
ritmGR.addEncodedQuery('active=true^ORstate=3'); // Filter for active or closed RITMs
ritmGR.addQuery('actual_start>=javascript:gs.dateGenerate(' + startDate.getDisplayValue() + ')');
ritmGR.addQuery('actual_end<=javascript&colon;gs.dateGenerate(' + endDate.getDisplayValue() + ')');
ritmGR.query();

// Iterate through the results to count RITMs per date
while (ritmGR.next()) {
    var actualStartDate = ritmGR.actual_start.getDisplayValue();
    var actualEndDate = ritmGR.actual_end.getDisplayValue();

    // Counting for each date from actual start to actual end
    var currentDate = new GlideDateTime(actualStartDate);
    var endDateTime = new GlideDateTime(actualEndDate);

    while (currentDate.compareTo(endDateTime) <= 0) {
        var currentDateString = currentDate.getLocalDate().getDisplayValue();

        if (!countPerDate[currentDateString]) {
            countPerDate[currentDateString] = 0;
        }
        countPerDate[currentDateString]++;

        currentDate.addDays(1); // Move to the next day
    }
}

// Output the counts per date
for (var date in countPerDate) {
    gs.info(date + ' count = ' + countPerDate[date]);
}

Thank you, please make helpful if you accept the solution. 

[tsoct]

Thank you @Yashsvi , this is working awesome!

Another question, how can i get the sum of quantity on sc_req_item table by date?

Example

1) RITM123456's Actual Start is July 9, 2024, and its Actual End is July 11, 2024, Quantity is 2

2) RITM789101's Actual Start is July 9, 2024, and its Actual End is July 9, 2024, Quantity is 3

the script count should return:
09 July count = 2. Quantity = 5
10 July count = 1. Quantity = 2
11 July count = 1. Quantity = 2

Thanks

[Yashsvi]

Hi @tsoct,

please try below script:

// Define the date range you want to query (adjust as needed)
var startDate = gs.dateGenerate('2024-07-09');  // Start date
var endDate = gs.dateGenerate('2024-07-11');    // End date

// Initialize an object to store results
var result = {};

// Query the sc_req_item table
var gr = new GlideAggregate('sc_req_item');
gr.addQuery('actual_start', '>=', startDate);
gr.addQuery('actual_start', '<=', endDate);
gr.groupBy('actual_start');
gr.addAggregate('SUM', 'quantity');

gr.query();

while (gr.next()) {
    var date = gr.getValue('actual_start');
    var formattedDate = gs.dateGenerate(date).getMonthLocalTime() + ' ' + gs.dateGenerate(date).getDayOfMonthLocalTime();
    var quantity = gr.getAggregate('SUM', 'quantity');
    
    result[formattedDate] = {
        count: gr.getRowCount(),
        quantity: quantity
    };
}

// Log or return the result
gs.info('Result: ' + JSON.stringify(result));

// Output the result
for (var date in result) {
    var data = result[date];
    gs.print(date + ' count = ' + data.count + '. Quantity = ' + data.quantity);
}

Thank you, please make helpful if you accept the solution.

[tsoct]

Thanks @Yashsvi ,

The formatted date is returning undefined.

Line below 

var date = gr.getValue('actual_start');

return 

2024-07-10 12:00:00

Then line below  

 var formattedDate = gs.dateGenerate(date).getMonthLocalTime() + ' ' + gs.dateGenerate(date).getDayOfMonthLocalTime();

return

undefined undefined