This topic contains 6 replies, has 0 voices, and was last updated by adallaway 7 years, 9 months ago.
-
AuthorPosts
-
January 24, 2017 at 12:42 pm #5002
adallawayHi all,
I want to get the sum of date differences between {custrecord_wevtlog_date_ended} and {created} grouped by employee ({custrecord_employee}).
Custom Record: Event Log
EventLog.custrecord_employee = “Foo Bar”
EventLog.created = “1/23/2017 1:51 pm”
EventLog.custrecord_wevtlog_date_ended = “1/23/2017 1:51 pm”
What I really need to be able to do in the result columns is SUM({custrecord_wevtlog_date_ended} – {created}) GROUP BY {custrecord_employee}.
I have tried a whole bunch of different combinations and end up with either an unexpected error being thrown or invalid expression errors.
SUM of TO_NUMBER({custrecord_wevtlog_date_ended} – {created}) ends up returning an empty string (I think it may be ignoring the time portion of the dates).
MAX of TO_CHAR({custrecord_wevtlog_date_ended} – {created}) shows “+000000000 00:59:02.000000” which is correct but I need it SUMmed not MAX.
Thanks for the help!
This is a cached copy. Click here to see the original post. -
January 24, 2017 at 12:50 pm #5003
david.smithIs this in the UI or script?
{custrecord_employee} where summary type is GROUP
formulanumeric( ROUND(({custrecord_wevtlog_date_ended} – {created}), 0) ) where summary type is SUM
-
January 24, 2017 at 1:01 pm #5004
adallawayIts via the UI. Ill try that out when I get back to the computer. (The only difference I notice is the ROUND) Thanks!
-
January 24, 2017 at 2:03 pm #5005
adallawayIf via the UI I use “Formula (Numeric)” with summary type “SUM” and formula “ROUND(({custrecord_wevtlog_date_ended} – {created}), 0)” I get “An unexpected error has occurred.” when attempting to run the search.
http://imgur.com/a/Bdo3B (screenshot failed to attach)
-
January 24, 2017 at 3:34 pm #5006
erictgrubaughWhen I’m trying to troubleshoot formulas, I typically make multiple Results columns, each progressively adding a step of the formula so I can see where things are breaking down. In your case, perhaps try something like:
Column 1: Formula (Numeric): {custrecord_wevtlog_date_ended}
Column 2: Formula (Numeric): {custrecord_wevtlog_date_ended}-{created}
Column 3: Formula (Numeric): ROUND({custrecord_wevtlog_date_ended}-{created})
If you continue getting Unexpected Errors when using all of these columns, then just start by running the search with only Column 1, then progressively adding each step to see which part of the formula is causing the Unexpected Error.
-
January 25, 2017 at 6:42 am #5007
adallawaySUM Formula (Numeric): {custrecord_wevtlog_date_ended} = ERROR: Invalid Expression
SUM Formula (Numeric): {created} = ERROR: Invalid Expression
SUM Formula (Numeric): {custrecord_wevtlog_date_ended} – {created} = An unexpected error has occurred.
SUM Formula (Numeric): ROUND(({custrecord_wevtlog_date_ended} – {created}), 1) = An unexpected error has occurred.
SUM Formula (Numeric): TO_NUMBER( ..any of the above .. ) = No error but blank result column
Tests with MAX to see what would happen:
MAX Formula (Numeric): {custrecord_wevtlog_date_ended} = ERROR: Invalid Expression
MAX Formula (Numeric): {created} = ERROR: Invalid Expression
MAX Formula (Numeric): {custrecord_wevtlog_date_ended} – {created} = +000000000 00:59:02.000000 (this is correct for MAX)
So it looks like the date diff works when using MAX but when using SUM is is unable to sum up all of those date diffs and throws an error. What am I missing here? Thanks
-
January 25, 2017 at 7:00 am #5008
adallawayGot it!
It looks like INTERVAL types in oracle cannot be summed.
If I convert the date difference into seconds then I can correctly return the summed # of seconds from each diff:
SUM (86400 * (trunc({custrecord_wevtlog_date_ended}) – trunc({created})) + to_number(to_char({custrecord_wevtlog_date_ended}, ‘sssss’)) – to_number(to_char({created},’sssss’)))
This results in the total # of seconds between all dates: 3,542.0
It’s unfortunate that I can’t use numtodsinterval() on top of the summed value to put it back into a more readable INTERVAL format but it’ll have to do.
Thanks for your time.
-
AuthorPosts
You must be logged in to reply to this topic.