CODEWITHSUNDEEP
sqlsql-server
Sanket Parchande
Sanket Parchande

Reputation: 984

Addition of total hours in SQL Server

In my database, I have a table with a column of type Float which represents the total work hours. I have to sum this value over all rows.

I can't use sum function, because 08.03 = 08 hour, 03 minute.

For example:

00.55 + 00.51=>  01.06

but I want 

00.55 + 00.51 =>  01.46

My query

Declare @TEMP1 table (TotalHours float)

insert into @TEMP1(TotalHours)
    select ol.HrWork TotalHours
    from OTLog ol
    where EmployeeId = 2048

Declare @attCount int
Declare @attSum int
set @attSum = 0;
set @attCount = (select count(*) from @TEMP1)

print @attCount

while (@attCount <> 0)
begin
    set @attSum =  @attSum + (select Top 1 (LEFT(TotalHours,1)*60)+(RIGHT(TotalHours,2)) TotalHours from @TEMP1);

    delete top(1) from @TEMP1;

    set @attCount = @attCount - 1;
    print @attSum;
end

print @attSum

select * 
from OTLog 
where EmployeeId = 2048

Any thoughts would be appreciated.

Upvotes: 0

Views: 937

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37367

Few issues with your problem:

  • first, float is not good choice for storing time information, look at date, time, datetime, timestamp, datetime2 - choose one of these, it will prevent such problems from occuring plus you have methods in SQL to work with such datatypes.

  • second - float is just approximation!, decimal stores exact values, you must be aware of that,

  • third - you need to be careful when dealing with decimal values in SQL Server, see Precision, scale, and Length (Transact-SQL) and this thread: Why is 199.96 - 0 = 200 in SQL?

After all, I can suggest this query to solve your problem:

select id, 
       sum(hours) + floor(sum(minutes) / 60) hours,
       sum(minutes) % 60 minutes
from (
  select id, 
         floor([time]) [hours], 
         cast([time] * 100 as int) % 100 [minutes]
  from tblFloat
) a group by id

See this SQL fiddle, there you can test difference between using float and decimal (see the differences in a queries).

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

should use proper data type for storing time and not float

You need to break the float time into hour & minute. sum() it and convert back to your float format

select  convert(int, total_minute / 60) +  (total_minute % 60) / 100.0
from
(
    select  total_minute = sum(convert(int ,([hour] * 60) + [minute]))
    from
    (
        select  hour = floor(TotalHours), minute = convert(int, TotalHours * 100) % 100
        from    @TEMP1
    ) d
) d

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

You can try to use left(col1,2) to get hour number,right(col1,2) get minute number.

then use SUM to get totle hours and minutes in a subquery.

Then use DATEADD to add time value.

CREATE TABLE T(
   col1 VARCHAR(10)
);


INSERT INTO T VALUES ('00.55');
INSERT INTO T VALUES ('00.51');

Query 1:

select FORMAT(DATEADD(minute,m,0)+ DATEADD(hour,h,0),'HH:mm:ss')
from (
  SELECT SUM(CAST(left(col1,2) AS INT)) h ,SUM(CAST(right(col1,2) AS INT)) m
  FROM T
) t1

Results:

|          |
|----------|
| 01:46:00 |

Upvotes: 1

Related Questions