sql server - Stored procedure to get date time difference -

location_table:

locid   locname -------------- 8       blooimng dale 

locvtypeassign_tbl:

locid       vtid ----------- ----------- 8           7 8           8 

transaction_tbl:

transactid   locid   vtid  dtime                       paydate ---------    -----   ----  -----------------------     ----------------------     7497         8       7     2013-06-28 14:39:34.000     2013-06-28 18:28:20.000 7500         8       8     2013-06-28 14:41:57.000     null 

if pass locid 8 how can sum of date difference of vtid 7 , vtid 8. finding date difference using formula:

sum(datediff(hour,t.paydate,t.deldate)) 

expected output:

location        datediff of 7       datediff of 8 blooming dale   2206:30321:73242    235:3373:56915 

is there way write stored procedure getting output?

i tried query this:

select convert(varchar(10),sum(datediff(hour,t.paydate,t.deldate)))+':'        +convert(varchar(10),sum(datediff(minute,t.paydate,t.deldate)% 60)) + ':'        +convert(varchar(10),sum(datediff(second,t.paydate,t.deldate)% 60))         ' hh:mm:ss' transaction_tbl t  t.locid=8 group vtid 

i getting output this:

hh:mm:ss ---------------- 2206:30321:73242 235:3373:56915 

you try following:

select l.locname location,   convert(varchar(10),sum(datediff(hour,t7.paydate,t7.deldate)))+':'    + convert(varchar(10),sum(datediff(minute,t7.paydate,t7.deldate)% 60)) + ':'    + convert(varchar(10),sum(datediff(second,t7.paydate,t7.deldate)% 60))    'datediff of 7',   convert(varchar(10),sum(datediff(hour,t8.paydate,t8.deldate)))+':'    + convert(varchar(10),sum(datediff(minute,t8.paydate,t8.deldate)% 60)) + ':'    + convert(varchar(10),sum(datediff(second,t8.paydate,t8.deldate)% 60))    'datediff of 8' location_tbl l join transaction_tbl t7 on t7.locid = l.locid , t7.vtid = 7 join transaction_tbl t8 on t8.locid = l.locid , t8.vtid = 8 l.locid = 8 

generalizing different set of datediff offsets left exercise reader. can check out pivot.