Search code examples
sqlsql-serverrollup

SQL - Calculate Hours sum using rollup

I am looking to get sum of all hours as summary in last row. I am using the following query to get result:

create table time_test (type varchar(10),time varchar(10))
insert into time_test values ('A','01:25')
insert into time_test values ('B','02:30')
insert into time_test values ('C','05:56')
insert into time_test values ('D','00:50')

--select * from time_test

SELECT
  type = ISNULL(type, 'Total'),
  time = substring((cast(sum(((cast(substring (time,2,1) as decimal)*60 + cast(substring (time,4,2) as decimal))/60)) as varchar(10))),1,2)+':' + cast((cast((round(((cast((((cast((substring((cast((sum(((cast(substring (time,2,1) as decimal)*60 + cast(substring (time,4,2) as decimal))/60))) as varchar(10))),4,2)) as int))*60)) as decimal)/100)),0)) as int)) as varchar(10))
FROM time_test
GROUP BY ROLLUP(type);

OUTPUT:

enter image description here

As you can see time is not coming correct beside this total calculation is working fine.

Problem: Please let me know where i am working wrong during showing the data.

Thanks in advance.

Solution

  • I did have similar requirement and solved it like this:

    select type, RTRIM(time/60) + ':' + RIGHT('0' + RTRIM(time%60),2) from
( 
    select type= ISNULL(type, 'Total'),
      time= SUM(DATEDIFF(MINUTE, '0:00:00', time))
    from time_test
    GROUP BY ROLLUP(type)
) x

    I believe this is more understandable and easier to trace

    type    time
A       1:25
B       2:30
C       5:56
D       0:50
Total   10:41

    Edit: Updated the query for hour can be more then 24 hours

    select type, RTRIM(time/60) + ':' + RIGHT('0' + RTRIM(time%60),2) from
( 
    select type= ISNULL(type, 'Total'),
      time= SUM(DATEDIFF(MINUTE, '0:00:00', 
        DATEADD(day, SUBSTRING(time,0,CHARINDEX(':',time,0)) / 24,
        DATEADD(hour, SUBSTRING(time,0,CHARINDEX(':',time,0)) % 24, 
        DATEADD(minute,SUBSTRING(time,CHARINDEX(':',time)+1,LEN(time)) +0, 0)) )))
    from time_test
    GROUP BY ROLLUP(type)
) x

    Example Result:

    A       1:25
B       2:30
C       5:56
D       70:50
Total   80:41