Post reply

sum of time durations

  • June 21, 2012 at 1:23 pm

    #263130

    Hello all,

    I'm new to the forum and also still learning about SQL.

    so, i'm working on creating a Database warehouse and receiving multiple file from different sources. The problem I'm having is that multiple column have time duration "00:00:00" and i'm being trying to find a way to change the datatype or something so i can use aggregation against that column (Sum, Avg, ect).

    please let me know if there is way to accomplish this and how

    thank you in advance for your help 😀

  • June 21, 2012 at 1:28 pm

    #1504193

    yunis.marrero (6/21/2012)

    Hello all,

    I'm new to the forum and also still learning about SQL.

    so, i'm working on creating a Database warehouse and receiving multiple file from different sources. The problem I'm having is that multiple column have time duration "00:00:00" and i'm being trying to find a way to change the datatype or something so i can use aggregation against that column (Sum, Avg, ect).

    please let me know if there is way to accomplish this and how

    thank you in advance for your help 😀

    Usually you'd do this via a DATEADD function, but you could also DATEDIFF it for seconds in a particular column... however, none of that helps you if it's a zero. It sounds more like you're fighting with dirty data than a formatting issue.

    Is the source data stored in milliseconds (or smaller)? Do you have access to the import ETL to be able to check the source? If so, storing it as DATETIME (or DATETIME2 for greater precision) would be helpful but you'd still need to get it to the system as something other than 0, which means it has to start at the source.

    I'm not really sure there's anything you can do with this until the ETL and source data is repaired.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 21, 2012 at 1:53 pm

    #1504211

    thanks for the fast reply

    ok, i received everyday a Excel data file from our PBX system with all calls received for the previous day. then i use SSIS to extract that data to our database and then do some clean up ect... below is an example on how the data is received

    the last column current data type is varchar and try it different type and i cant get to work

  • June 21, 2012 at 2:03 pm

    #1504217

    You can use the DATEDIFF function to convert the time to integer seconds and then sum that up.

    select

    Duration_Seconds = sum( datediff(ss,0,x.call_duration) )

    from

    MyTable

  • June 21, 2012 at 2:49 pm

    #1504240

    thanks that worked...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply