SQL Server - time addition and time subtraction

Asked By mohd ghouse on 02-May-13 07:21 AM
i need to sum a total time of one column with related id and i want to substract the total time withe\ the actual time which is present in different table
that 
user id=1
sendind time
00:00:06
00:05:00

Total:00:05:06 
and i want to substract from another table
that is

total durtion is

01:05:06 -00:05:06

Ans is : 01:00:00
Please any one can help me out with this.........
Sandeep Mittal replied to mohd ghouse on 12-May-13 12:53 AM
declare @tab1 table(userid int, tt time)
declare @tab2 table(userid int, tt time)
insert into @tab1
select 1, '00:00:06' union all
select 1, '00:05:00'
insert into @tab2
select 1, '01:05:06'
 
;with cte1 as(
  select userid, sum((DATEPART(hh, tt) * 60*60) + (DATEPART(mi, tt) * 60) + DATEPART(ss, tt)) secs from @tab1 group by userid
), cte2 as(
  select userid, sum((DATEPART(hh, tt) * 60*60) + (DATEPART(mi, tt) * 60) + DATEPART(ss, tt)) secs from @tab2 group by userid
)
select userid, cast(cast(secs/(3600) as varchar) +  ':' + cast((secs%3600)/60 as varchar) + ':' + cast(secs%60 as varchar) as time)as tt
from (
  select  a.userid, a.secs-b.secs as secs
  from    cte2 a
  left join cte1 b on a.userid = b.userid
) tab