Sum duration over 24 - 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 duration over 24

Author

Topic

jmersing
Yak Posting Veteran

57 Posts

Posted - 2010-10-30 : 07:22:45

Not sure if I am going about this the right way but what I'm trying to do is sum the amount of hours where starttime is subracted from endtime to give a distinct row with a sum of hours as a total.

I'm getting multiple rows with durations in each vs. a total.

Select distinct organizationname, activityname,
(Convert(varchar(10),Datediff(mi,starttimelocal,endtimelocal)/ 60) +':' +
Convert(varchar(10),Datediff(mi,starttimelocal,endtimelocal )%60)) as Duration
From V_RPT_PLANNEDACTIVITIES
Where convert(varchar,workdaylocal,110) = '11-01-2010'
Group By organizationname, activityname,Convert(varchar(10),Datediff(mi,starttimelocal,endtimelocal)/ 60) +':' +
Convert(varchar(10),Datediff(mi,starttimelocal,endtimelocal )%60)
Order By organizationname, activityname

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-30 : 09:23:29

There's nothing there to do a sum.
maybe

Select distinct organizationname, activityname,
(Convert(varchar(10),sum(Datediff(mi,starttimelocal,endtimelocal))/ 60) +':' +
Convert(varchar(10),sum(Datediff(mi,starttimelocal,endtimelocal))%60)) as Duration
From V_RPT_PLANNEDACTIVITIES
Where convert(varchar,workdaylocal,110) = '11-01-2010'
Group By organizationname, activityname
Order By organizationname, activityname

Might end up with an overflow depending on the data

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jmersing
Yak Posting Veteran

57 Posts

Posted - 2010-10-30 : 09:31:22

Seems to work, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources