CODEWITHSUNDEEP
sqlsql-server-2008
user3035016
user3035016

Reputation: 51

Sum of hour and minutes in SQL Server 2008

I have written one query,but it not giving correct answer.I want to display like this 5 Hour 58 minutes + 7 Hour 58 minutes=13 hour 56 minutes(answer should 13.56),but this below query giving 15.09 .

Please reply me with any other solution.(here Effort1 is 5 Hour column,Effort1Minutes is 58 minute column and same Effort2 is 7 Hour column,Effort2Minutes is 58 minute column .

 select CAST((SUM(Effort1)*60+SUM(Effort1Minutes))/60 as float) +  
       (CAST((SUM(Effort1)*60+SUM(Effort1Minutes))% 60 as float)) / 100 +   
        CAST((SUM(Effort2)*60+SUM(Effort2Minutes))/60 as float) +  
       (CAST((SUM(Effort2)*60+SUM(Effort2Minutes))% 60 as float)) / 100               
 from TimesheetDetails

Upvotes: 3

Views: 2643

Answers (1)

Naresh Pansuriya
Naresh Pansuriya

Reputation: 2045

please tried below query

    select CONVERT(decimal(18,2),SUM(Effort1 + Effort2)) + 
    SUM(Effort1Minutes + Effort2Minutes) / 60 + 
    CONVERT(decimal(18,2), (SUM(Effort1Minutes + Effort2Minutes) % 60) / 100.00)
from TimesheetDetails

I had tested with this query with SQL server 2008 as well as with SQL Fiddle

Upvotes: 1

Related Questions