Newbie needing help - 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

Newbie needing help

Author

Topic

albertramsbottom
Starting Member

4 Posts

Posted - 2012-04-10 : 08:29:36

I have a table called f2f and a duration filed called "duration" which is in minutes and I need to translate this too hours

my current sql is:

SELECT SUM(duration) /60 as "Total Hours"
FROM prefix_f2f_sessions

this gives me the result as 43.000 hours, I think I would like to chop off the .000 and maybe divide it by 7.5 so I get the days

Can anybody help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:28:03

convert it to int to drop of decimal part or use ROUND(field,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cheaptrix
Starting Member

1 Post

Posted - 2012-04-10 : 15:30:58

SELECT CONVERT(INT,((SUM(duration) / 60) / 7.5)) AS Total_Days
FROM ....

albertramsbottom
Starting Member

4 Posts

Posted - 2012-04-11 : 04:39:06

Many thanks

It worked apart from the fact that the convert to interger didnt seem to work

I now have

SELECT SUM(duration) /60 /7.5 as "Total FTE Days"
FROM prefix_facetoface_sessions

Which does give me the days be it

5.73333333

Many thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-04-11 : 06:40:31

ah -- if you are dividing by a FLOAT or DECIMAL (7.5) the result will be a FLOAT or DECIMAL.

If you wanted the number of total days the most natural way would to express it is probably to use FLOOR()

Something like


DECLARE @duration INT
SET @duration = 1000

SELECT FLOOR(@duration / 60 / 7.5)

Except you'd do:



SELECT FLOOR(SUM(duration) /60 /7.5) as "Total FTE Days"
FROM prefix_facetoface_sessions

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources