This topic contains 6 replies, has 0 voices, and was last updated by adallaway 7 years, 9 months ago.

  • Author
    Posts
  • #5002 Score: 0

    adallaway
    • Contributions: 0
    • Level 1

    Hi 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.

  • #5003 Score: 0

    david.smith
    • Contributions: 0
    • Level 1

    Is 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

  • #5004 Score: 0

    adallaway
    • Contributions: 0
    • Level 1

    Its via the UI. Ill try that out when I get back to the computer. (The only difference I notice is the ROUND) Thanks!

  • #5005 Score: 0

    adallaway
    • Contributions: 0
    • Level 1

    If 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)

  • #5006 Score: 0

    erictgrubaugh
    • Contributions: 0
    • Level 1

    When 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.

  • #5007 Score: 0

    adallaway
    • Contributions: 0
    • Level 1

    SUM 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

  • #5008 Score: 0

    adallaway
    • Contributions: 0
    • Level 1

    Got 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.

You must be logged in to reply to this topic.