How to calculate Time field sum

• Vijji-429978

  SSC Eights!

  Points: 983

  More actions

  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

• ashokdasari

  SSC-Addicted

  Points: 467

  More actions

  May 26, 2009 at 8:41 am

  #999926

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

• Vijji-429978

  SSC Eights!

  Points: 983

  More actions

  May 26, 2009 at 8:50 am

  #999928

  Data Type is Char

• Lowell

  SSC Guru

  Points: 323482

  More actions

  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!

• ashokdasari

  SSC-Addicted

  Points: 467

  More actions

  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

• Lowell

  SSC Guru

  Points: 323482

  More actions

  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!

• Vijji-429978

  SSC Eights!

  Points: 983

  More actions

  May 26, 2009 at 2:11 pm

  #1000181

  Thank you ashokdasari, it worked perfectly..