Summarize hours values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Summarize hours values

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-06-07 : 04:18:27

Hello all
I have to summarize several time values like:

2h03m
3h14m
...

to obtain the total hours (and minutes).

This using SQL Server 2008 R2.

How can I accomplish this?

Thanks a lot.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 04:39:07

something like provided your time value format in column is consistent and wont cross a day mark


SELECT CAST((DATEDIFF(dd,0,SumDate) * 24) + DATEPART(hh,SumDate) AS varchar(5)) + 'h' + DATENAME(mi,SumDate) + 'm'
FROM
(
SELECT DATEADD(mi,SUM(DATEDIFF(mi,0,CAST(REPLACE(REPLACE(TimeColumn,'h',':'),'m','') AS datetime))),0) AS SumDate
FROM table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 04:47:50

In case it goes over a day like

28h30m etc which by itself would be kind of aggregated value you can this


SELECT COALESCE(CAST((HourPart + (MinPart/60)) AS varchar(5)) + 'h','') + CAST((MinPart % 60) AS varchar(5)) + 'm'
FROM(
SELECT CAST(CASE WHEN CHARINDEX('h',TimeCol) > 0 THEN LEFT(TimeCol,CHARINDEX('h',TimeCol)-1) ELSE NULL END AS int) AS HourPart,
      CAST(REPLACE(CASE WHEN CHARINDEX('h',TimeCol) > 0 THEN STUFF(TimeCol,1,CHARINDEX('h',TimeCol),'') ELSE TimeCol END,'m','') AS int) AS MinPart
FROM table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-06-07 : 05:33:44

Thank you Visakh, your first solution works well.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 05:34:23

cool...you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources