How to group by date portion of sys_created_on in a GlideAggregate

[gjz1]

I have an audit log that may have multiple rows per day per person and I need to find the last row per person per day.  For example:

Person A: 2020-01-21 09:00:00
Person A: 2020-01-21 08:45:00
Person A: 2020-01-21 07:00:00
Person B: 2020-01-21 09:00:00

Person A: 2020-01-20 08:45:00
Person B: 2020-01-20 07:00:00
Person B: 2020-01-20 06:59:00

Person A: 2020-01-19 09:00:00
Person B: 2020-01-19 09:00:00

What I want to see in the output is

Person A: 2020-01-21 09:00:00
Person B: 2020-01-21 09:00:00
Person A: 2020-01-20 08:45:00
Person B: 2020-01-20 07:00:00
Person A: 2020-01-19 09:00:00
Person B: 2020-01-19 09:00:00

I want to group by person and date (not time) - but I can't figure out how to group by the date portion of sys_created_on.  Does anyone know how to do that?

Here's my query, which works fine except for agg.groupBy('sys_created_on').  I have an encoded query which will be removed in the final version, I just wanted to limit the rows while testing.

var agg = new GlideAggregate('u_hourly_employee_time_tracking');
agg.addEncodedQuery("sys_created_onBETWEENjavascript:gs.dateGenerate('2020-01-19','00:00:00')@javascript:gs.dateGenerate('2020-01-21','09:01:00')");
agg.addAggregate('MAX', 'sys_created_on');
agg.groupBy('u_employee_name');
agg.groupBy('u_status');
agg.groupBy('sys_created_on');
agg.query();
while(agg.next()) {
    answer = agg.getAggregate('MAX', 'sys_created_on');
    gs.print('row: ' + agg.u_employee_name + '  ' + answer + '  ' + agg.u_status);
}

[Nagasai]

Not possible to GlideAggregate dateTime fields as glideDate. here is custom logic -

Create few records in sc_task table with work_start and work_end field values. 

Update addEncodedQuery as per your requirement

Now execute the below script.

this.dataMap = {};

var taskGr = new GlideRecord("sc_task");
taskGr.addEncodedQuery("work_startBETWEENjavascript:gs.dateGenerate('2022-01-20','00:00:00')@javascript:gs.dateGenerate('2022-01-24','23:59:59')^work_endBETWEENjavascript:gs.dateGenerate('2022-01-20','00:00:00')@javascript:gs.dateGenerate('2022-01-24','23:59:59')");
taskGr.orderBy("work_start");
taskGr.orderBy("work_end");
taskGr.query();
while (taskGr.next()) {

var number = taskGr.getValue("number");

var startTime = taskGr.work_start;
var endTime = taskGr.work_end;
var start = new GlideDateTime(startTime).getDate();
var end = new GlideDateTime(endTime).getDate();

//gs.info(startTime+"-"+endTime);
//gs.info(start+" ** "+ end);

var startEnd = start + "-" + end;
if (this.dataMap.hasOwnProperty(startEnd))
this.dataMap[startEnd].taskNums.push(number);
else {
var obj = {};
obj.taskNums = [number];
this.dataMap[startEnd] = obj;
}

}
//gs.info(this.dataMap)
gs.info(JSON.stringify(this.dataMap));

for (var tasks in this.dataMap) {
var taskData = this.dataMap[tasks];
gs.info("In for " + taskData);

var changeSysId = "";
for (var k in taskData.taskNums) {
var taskNum = taskData.taskNums[k];
gs.info(taskNum);
var sctaskGr = new GlideRecord("sc_task");
sctaskGr.addQuery("number", taskNum);
sctaskGr.query();
if (sctaskGr.next()) {
   gs.info(sctaskGr.work_start + " | " + sctaskGr.work_end);

}
}

}

[garyopela]

Hello, there is actually a way I just figured out. You can use addTrend. Here is an example:

var grChatDur = new GlideAggregate('interaction');

var chatDurQuery = "state=closed_complete^type=chat^assignment_group.name=HR Contact Center";

grChatDur.addEncodedQuery(chatDurQuery);

grChatDur.addAggregate('COUNT');

grChatDur.addAggregate('SUM', 'duration');

grChatDur.addTrend('sys_created_on','date');

grChatDur.addAggregate('AVG', 'duration');

grChatDur.setGroup(false);

grChatDur.setOrder(false);

grChatDur.query();

while (grChatDur.next()){

    gs.info('Date: ' + grChatDur.getValue('timeref') + ' Count: ' + grChatDur.getAggregate('COUNT') + ' Sum: ' + grChatDur.getAggregate('SUM', 'duration') + ' Average: ' + grChatDur.getAggregate('AVG', 'duration'));

}

I hope that works 🙂 It looks good at first for me at least.