Post reply

How to calculate Time field sum

  • May 26, 2009 at 8:36 am

    #131939

    Hi

    I have a the timetaken data like below and i need to sum the column, sum of the column is not helping me for the result, because last 2 digits are minutes, can someone please help me.

    TimeTaken

    00100

    00125

    00030

    00030

    00100

    00100

    00100

    00030

    Thank you,

    Regards

    Vijji

  • May 26, 2009 at 8:41 am

    #999926

    May I know what is the data type used for column TimeTaken

  • May 26, 2009 at 8:50 am

    #999928

    Data Type is Char

  • May 26, 2009 at 8:51 am

    #999930

    the trick is you've got to take the minutes, i.e. "25", turn them into a decimal and divide by 60 so you get the part of an hour

    create table #Example(TimeTaken char(5) )

    INSERT INTO #Example

    SELECT '00100' UNION ALL

    SELECT '00125' UNION ALL

    SELECT '00030' UNION ALL

    SELECT '00030' UNION ALL

    SELECT '00100' UNION ALL

    SELECT '00100' UNION ALL

    SELECT '00100' UNION ALL

    SELECT '00030'

    --assuming this column cna take 999 hours + 99 minutes

    select

    convert(Decimal(12,2),left(TimeTaken,3)) as Hours,

    convert(Decimal(12,2),Right(TimeTaken,2)) As PartOfHours from #Example

    --results like this:

    --HoursPartOfHours

    --1.000.00

    --1.0025.00

    --now make Part Of Hours a decimal;, /min / 60

    SELECT (Hours + (PartOfHours / 60.0)) As TotalHours

    FROM (

    select

    convert(Decimal(12,2),left(TimeTaken,3)) as Hours,

    convert(Decimal(12,2),Right(TimeTaken,2)) As PartOfHours from #Example

    ) MyAlias

    --results like this

    --TotalHours

    --1.000000

    --1.416666

    --now finally get the sum...converting to decimal 12,2 because i don't want too many positions after the decimal

    SELECT CONVERT(decimal(12,2),SUM((Hours + (PartOfHours / 60.0)))) As TotalHours

    FROM (

    select

    convert(Decimal(12,2),left(TimeTaken,3)) as Hours,

    convert(Decimal(12,2),Right(TimeTaken,2)) As PartOfHours from #Example

    ) MyAlias

    --results like this

    --TotalHours

    --6.92

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 26, 2009 at 8:54 am

    #999931

    Try This way

    DECLARE @Temp Table

    (TimeTaken CHAR(5)

    )

    INSERT INTO @Temp

    SELECT '00100'

    UNION ALL

    SELECT '00125'

    UNION ALL

    SELECT '00030'

    UNION ALL

    SELECT '00030'

    UNION ALL

    SELECT '00100'

    UNION ALL

    SELECT '00100'

    UNION ALL

    SELECT '00100'

    UNION ALL

    SELECT '00030'

    SELECT SUM(CONVERT(INT,SUBSTRING(TimeTaken,1,3)))

    + SUM(CONVERT(INT,SUBSTRING(TimeTaken,4,2)))/60 Hrs,

    SUM(CONVERT(INT,SUBSTRING(TimeTaken,4,2)))%60 AS Mins

    FROM @Temp

  • May 26, 2009 at 8:57 am

    #999932

    nice solution ashokdasari

    , i didn't realize the OP might still want the data in Hours and Minutes.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 26, 2009 at 2:11 pm

    #1000181

    Thank you ashokdasari, it worked perfectly..

Viewing 7 posts - 1 through 6 (of 6 total)

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