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