Aggregation on duration fields

[margie_joyce]

Hi there,

I'm wanting to do an aggregate query on a duration field, but I'm getting strange results.

I have a user defined table which has a duration field called u_effort. I want to sum the records for a particular user, for a particular date and for a particular type

var rec = new GlideAggregate("u_time_recording");
rec.addQuery("u_assigned_to", tsUser); <---- the user i am summing for
rec.addQuery("u_type", tsType); <---- the type i am summing for
rec.addquery("u_work_date", tsPeriodEnd); <---- the date i am summing for
rec.addAggregate("SUM", "u_effort"); <---- the field i am trying to sum
rec.groupBy("u_type");
rec.query();
if (rec.next()) {
var testField = rec.getAggregate("SUM", "u_effort");
gs.log("The aggregate value for " + rec.u_type + " is " + testField);
}

My data looks like this:

21-11-2008 1 day 15 minutes
22-11-2008 7 hours 30 minutes
22-11-2008 30 minutes

but the aggregate values i get out are always 1970-01-01 00:00:00 (which I read as essentially zero).

My questions:

1 - Is it possible to do aggregates on duration fields?
2 - If the answer to 1 is "Yes", what am I doing wrong here?
3 - If the answer to 1 is "No", how would I go about manually summing duration fields?

Best regards and thanks,
margie

[john_roberts]

Try this to get the duration to display better. But it sounds like you still have a problem getting results back. You might want to try a simple query first to make sure you are getting the right results, then move to the aggregate.
Since you have a groupBy you'll probably also need to change the if (rec.next()) to while (rec.next()) to make sure you process all the results.

var rec = new GlideAggregate("u_time_recording");
rec.addQuery("u_assigned_to", tsUser);
rec.addQuery("u_type", tsType);
rec.addquery("u_work_date", tsPeriodEnd);
rec.addAggregate("SUM", "u_effort");
rec.groupBy("u_type");
rec.query();
if (rec.next()) {
  //need to create a duration object first
  var dur = new Packages.com.glide.glideobject.GlideDuration();
  //set the value
  dur.setValue(rec.getAggregate("SUM", "u_effort"));
  //show the display value, or use getDurationValue()
  gs.log("The aggregate value for " + rec.u_type + " is " + dur.getDisplayValue());
}

[margie_joyce]

Thanks for your help. I've been back and revised the query and determined that I'm now getting the correct data. I'm still not able to get a sensible aggregate value out. (Note that I've removed the groupBy as I did not need it.)

If I use:

var rec = new GlideAggregate("u_time_recording");
rec.addQuery("u_assigned_to", tsUser);
rec.addQuery("u_type", tsType);
rec.addQuery("u_work_date", tsPeriodEnd);
rec.addAggregate("COUNT");
rec.query();
while (rec.next()) {
var counter = rec.getAggregate("COUNT");
gs.log("Got " + counter + " records");
}

I get the correct COUNT returned for my criteria.

However, if I modify it to this:

var rec = new GlideAggregate("u_time_recording");
rec.addQuery("u_assigned_to", tsUser);
rec.addQuery("u_type", tsType);
rec.addQuery("u_work_date", tsPeriodEnd);
rec.addAggregate("SUM", "u_effort");
rec.query();
while (rec.next()) {
var dur = new Packages.com.glide.glideobject.GlideDuration();
dur.setValue(rec.getAggregate("SUM", "u_effort"));
gs.log("Got " + dur.getDisplayValue());
}

I get one record returned for *each row*. That is, I'm not getting an aggregate SUM, just each individual row.

Am I doing something wrong with the SUM?

Any help gratefully received ...

margie

[john_roberts]

It should work if you put the groupBy back in. You need to tell the aggregate what to total.

rec.groupBy("u_assigned_to");

[merbs]

I realize this post is REALLY old now - but was this ever resolved?