SQL Server Performance

sumarize a char field

Discussion in 'General Developer Questions' started by boutwater, Jan 16, 2007.

  1. boutwater Member

    We have a table that is getting written to by a third party app. It is a varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> and is used to keep time (hh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s or 04:32:42). We would like to sum this column. Is there any easy way to do this? I'm using the following query right now, but it's clunky, and doesn't give the correct info in the case where one of the values sums to more then 59 (i can get 04:32:67, which should be 04:33:07).<br /><br />select tag,(right('0' + cast(sum(cast(SUBSTRING(deltatime,1,2) as int)) as varchar),2) +':'+ right('0' + cast(sum(cast(SUBSTRING(deltatime,4,2) as int)) as varchar),2) +':'+ right('0' + cast(sum(cast(SUBSTRING(deltatime,7,2) as int)) as varchar),2)) as time<br />from alarmlog<br />group by tag<br /><br />Thanks in advance,<br /><br />Ben
  2. khtan New Member

    this will give you in days - hours - minutes - seconds


    selectright(convert(varchar(20), dateadd(second, sum(datediff(second, 0, convert(datetime, col))), 0), 120), 11)
    from
    (
    select'04:32:42' as colunion all
    select'22:33:44' as colunion all
    select'12:34:56' as col
    ) a



    KH
  3. khtan New Member

    this in detail breakdown of the steps



    selectright(convert(varchar(20), dte, 120), 11)
    from
    (
    selectdte = dateadd(second, tot_secs, 0)
    from
    (
    selecttot_secs = sum(secs)
    from
    (
    selectsecs = datediff(second, 0, dt)
    from
    (
    selectdt = convert(datetime, col)
    from
    (
    select'04:32:42' as colunion all
    select'22:33:44' as colunion all
    select'12:34:56' as col
    ) a
    ) b
    ) c
    ) d
    ) e



    KH
  4. boutwater Member

    That's awesome! Thank you. I'm assuming that the three selects at the end are just to test the query. When I tried replacing them with my column, i got the error: Syntax error converting datetime from character string. Here is my query:<br /><br />selectright(convert(varchar(20), dateadd(second, sum(datediff(second, 0, convert(datetime, col))), 0), 120), 11)<br />from<br />(<br />selectdeltatime col from alarmlog//deltatime is a varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> column<br />) a<br /><br />Can you let me know what I can do to fix this? Thanks,<br /><br />Ben

Share This Page