none
Have a sum of 2 time formmated column as a new column

    Question

  • I have 2 column in my query that return time formatted value for example, The first column returns 3:20 and the next one return 2:30 , I like to have a new column that return the sum of these 2 column that is in this case: 5:50.


    Column 1:

    Select     ltrim(xfer_1_elapsed/60) +':' + REPLACE(SPACE(CASE WHEN xfer_1_elapsed%60< 10 THEN 1 ELSE 0 END) + LTRIM(xfer_1_elapsed%60), ' ', '0') as xfer_1_elapsed,


    column 2:

    case when second_rep != orig_rep
            then ltrim((xfer_2_elapsed - xfer_1_elapsed)/60) +':' + REPLACE(
                        SPACE(CASE WHEN (xfer_2_elapsed - xfer_1_elapsed)%60 < 10 THEN 1 ELSE 0 END), ' ', '0') + LTRIM((xfer_2_elapsed - xfer_1_elapsed)%60)
        else null end as xfer_2_elapsed

    what should I write for column 3 to do the sum? (please reply if you can write it)
    Wednesday, February 29, 2012 5:54 PM

Answers

  • It looks like you originally have two values with elapsed time in minutes, e.g.

    xfer_1_elapsed -- first time elapsed in minutes (or seconds)

    case when second_rep <> orig_rep then (xfer_2_elapsed - xfer_1_elapsed) else 0 end as SecondTimeElapsed (in minutes or seconds)

    Therefore it will be easier to work with these two times instead of formatting already formatted, so

    CROSS APPLY (SELECT xfer_1_elapsed -- first time elapsed in minutes (or seconds)
    
    + case when second_rep <> orig_rep then (xfer_2_elapsed - xfer_1_elapsed) else 0 end as TotalTimeElapsed ) TotalTime

    --------- Your time then will be LTRIM(TotalTime.TotalTimeElapsed/60) + ':' + LTRIM(TotalTime.TotalTimeElapsed%60)

    In other words, I suggest to add TotalTimeElapsed using CROSS APPLY and then format it in the main query same way you were formatting other two columns.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi NModerator Wednesday, February 29, 2012 6:33 PM
    • Marked as answer by nikoo56 Thursday, March 01, 2012 12:10 AM
    Wednesday, February 29, 2012 6:32 PM
    Moderator

All replies

  • It looks like you originally have two values with elapsed time in minutes, e.g.

    xfer_1_elapsed -- first time elapsed in minutes (or seconds)

    case when second_rep <> orig_rep then (xfer_2_elapsed - xfer_1_elapsed) else 0 end as SecondTimeElapsed (in minutes or seconds)

    Therefore it will be easier to work with these two times instead of formatting already formatted, so

    CROSS APPLY (SELECT xfer_1_elapsed -- first time elapsed in minutes (or seconds)
    
    + case when second_rep <> orig_rep then (xfer_2_elapsed - xfer_1_elapsed) else 0 end as TotalTimeElapsed ) TotalTime

    --------- Your time then will be LTRIM(TotalTime.TotalTimeElapsed/60) + ':' + LTRIM(TotalTime.TotalTimeElapsed%60)

    In other words, I suggest to add TotalTimeElapsed using CROSS APPLY and then format it in the main query same way you were formatting other two columns.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi NModerator Wednesday, February 29, 2012 6:33 PM
    • Marked as answer by nikoo56 Thursday, March 01, 2012 12:10 AM
    Wednesday, February 29, 2012 6:32 PM
    Moderator
  • Thank you so much for your help nobody in any forum could help me.
    Thursday, March 01, 2012 12:10 AM