CODEWITHSUNDEEP
sqlsql-servert-sql
Dolu bolu
Dolu bolu

Reputation: 189

SUM of a group of time differences in T-SQL?

I want to sum all the time differences to show the total hours worked. 

select 
    aaaa
from 
    employee B 
inner join 
    (select 
         s.emp_reader_id,
         Sum(case  when  s.in_time is not null and s.out_time is not null  and s.shift_type_id=5 and  LOWER(DATENAME(dw, [att_date]))='friday'then   
cast(datediff(minute,'00:00:00', '23:59:59') / 60 +
     (datediff(minute,'00:00:00', '23:59:59') % 60 / 100.0) as decimal(7, 4)
           ) end) as aaaa
     from 
         Daily_attendance_data s 
     left outer join 
         employee bb on s.emp_reader_id = bb.emp_reader_id
     where 
         att_date between '2018-10-01' and '2018-10-31' 
         and s.emp_reader_id = 1039
     group by 
         s.emp_reader_id) A on B.emp_reader_id = A.emp_reader_id

Current output:

aaaa
47.1800

which gives the list of times by hours but then I want to sum it up to a grand total.

It would just total

Sample data : 

23:59
23:59

Expected output:

47.58

Upvotes: 1

Views: 82

Answers (2)

D-Shih
D-Shih

Reputation: 46239

If your date type is DateTime.

you can try to let your value split two part.

  1. hours get value need to condider carry from minutes so do SUM(intpart) + SUM(floatpart) / 60
  2. minutes get value from SUM(floatpart) % 60

look like this.

SELECT concat(SUM(intpart) + SUM(floatpart) / 60,':', SUM(floatpart) % 60)
FROM (
    SELECT cast(SUBSTRING (cast(col as varchar),0,3) as int) intpart,
           cast(SUBSTRING (cast(col as varchar),CHARINDEX(':',col) +1,2)as int) floatpart
    FROM T
) t1

sqlfiddle

Upvotes: 2

Hadi
Hadi

Reputation: 37348

I think you should convert all times to seconds, calculate the SUM then convert the total to HH:mm:ss.

  1. Calculate The SUM of seconds

    DECLARE @TimeinSecond as integer = 0

select @TimeinSecond = Sum(DATEDIFF(SECOND, '0:00:00', [WorkHrs]))
from Daily_attendance_data

  2. Convert the HH:mm:ss Format

    SELECT RIGHT('0' + CAST(@TimeinSecond / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(@TimeinSecond % 60 AS VARCHAR),2)

References

Upvotes: 3

Related Questions