sum hours and minutes - 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

General SQL Server Forums

New to SQL Server Programming

sum hours and minutes

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2009-07-21 : 12:11:44

Hi All.
I have two columns Hours and Minutes. And would like to SUM all together to receive result like HH:mm.
Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-21 : 12:16:41

declare @t table (hours int, minutes int)
insert @t
select 10,35 union all
select 13,01 union all
select 20,59 union all
select 5,35

SELECT Right('00' + Rtrim(Cast(hours AS CHAR(2))),2) + ':' + Right('00' + Rtrim(Cast(minutes AS CHAR(2))),2) 
FROM   @t

EDIT: Why do you want to store data in this format, instead of a datetime type?

eugz
Posting Yak Master

210 Posts

Posted - 2009-07-21 : 12:26:09

Hi vijayisonly, thaks for replay.
The type data of columns Hours and Minutes are INT. And I need to calculate SUM of both columns Hours and Minutes.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 20:03:29

like this ?


select total_minues / 60 as the_hour, 
       total_mintues % 60 as the_minute,
       right('00000' + convert(varchar(10), total_minues / 60), 5) + ':' +
       right('00' + convert(varchar(10), total_minues % 60), 2) as hhmm
from
(
    select sum(hour) * 60 + sum(minutes) as total_minutes
    from   yourtable
) d

KH
[spoiler]Time is always against us[/spoiler]

eugz
Posting Yak Master

210 Posts

Posted - 2009-07-21 : 22:26:39

khtan, thanks for help.
That exactly that I'm looking for.
Can you briefly explaine why you use '00000' for hour and '00' for minutes? How wokrs d at the end of code.
Thaks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 22:36:03

the summation of hours may exceed 2 digits. I just assume it will not exceed 5 digits.

As for the minutes, it will always be < 60, so 2 digits is the max.

d is the alias name for the derived table

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-22 : 02:18:00

Continue here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129759

N 56°04'39.26"
E 12°55'05.63"

eugz
Posting Yak Master

210 Posts

Posted - 2009-07-22 : 16:13:12

Hi khtan.
I have one more question. How to insert to your code BirthDate datetime field. That field from same table which has Hours and Minutes fields.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 18:26:43

The data type for BirthDate is datetime ? What do you want to do with this column ?

KH
[spoiler]Time is always against us[/spoiler]

eugz
Posting Yak Master

210 Posts

Posted - 2009-07-22 : 21:08:42

Base on your code I created stored procedure that will I use for crystal report. Now I would like grouping report by year, by month for total Hours and Minutes for Lateness.(BirthDate is my mistake).
Thanks for help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 21:31:37

the year and month is based on which column ?

KH
[spoiler]Time is always against us[/spoiler]

eugz
Posting Yak Master

210 Posts

Posted - 2009-07-22 : 22:04:28

The year and month is based on EmpCallDate.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 22:59:28

[code]
select datepart(year, call_date) as call_year,
datepart(month, call_date) as call_month,
total_minues / 60 as the_hour,
total_mintues % 60 as the_minute,
right('00000' + convert(varchar(10), total_minues / 60), 5) + ':' +
right('00' + convert(varchar(10), total_minues % 60), 2) as hhmm
from
(
select dateadd(month, datediff(month, 0, EmpCallDate), 0) as call_date,
sum(hour) * 60 + sum(minutes) as total_minutes
from yourtable
group by dateadd(month, datediff(month, 0, EmpCallDate), 0)
) d
[/code]

KH
[spoiler]Time is always against us[/spoiler]

eugz
Posting Yak Master

210 Posts

Posted - 2009-07-24 : 10:36:44

khtan, thanks a lot. I appreaciate for help.
I don't clearly understand about alias d. You give alias d to derived table. How it references to first part of code if first part doesn't has same alias?
Thanks.

eugz
Posting Yak Master

210 Posts

Posted - 2009-07-26 : 15:37:39

Hi kntan.
Is it posible to order that select by call_manth? I tried to do and got error message:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-26 : 15:54:08

You have to alias a derived table like this
(
select dateadd(month, datediff(month, 0, EmpCallDate), 0) as call_date,
sum(hour) * 60 + sum(minutes) as total_minutes
from yourtable
group by dateadd(month, datediff(month, 0, EmpCallDate), 0)
)

you can't put an order by clause in it, ever. You can order the outer query, though. If this is a view try

select top 100 percent datepart(year, call_date) as call_year,
datepart(month, call_date) as call_month,
total_minues / 60 as the_hour,
total_mintues % 60 as the_minute,
right('00000' + convert(varchar(10), total_minues / 60), 5) + ':' +
right('00' + convert(varchar(10), total_minues % 60), 2) as hhmm
from
(
select dateadd(month, datediff(month, 0, EmpCallDate), 0) as call_date,
sum(hour) * 60 + sum(minutes) as total_minutes
from yourtable
group by dateadd(month, datediff(month, 0, EmpCallDate), 0)
) d

Jim

eugz
Posting Yak Master

210 Posts

Posted - 2009-07-27 : 10:44:19

Thanks jimf.
I got it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources