March 3, 2006 at 11:49 am
Hi All,
I have a field (varchar(8)) in a history table that gives me the duration of calls on my team in HH:MM:SS format. I want to write a query that gives the sum of durations by customer.
I wrote the following to give the results in total seconds:
SELECT CONTACT1.COMPANY,
sum(datediff(ss,'0:0:0',CONTHIST.DURATION)) as seconds
FROM CONTACT1 INNER JOIN CONTHIST ON CONTACT1.ACCOUNTNO = CONTHIST.ACCOUNTNO and isdate(CONTHIST.DURATION)=1
where (CONTHIST.RESULTCODE LIKE 'CT%') AND
(CONTHIST.ONDATE BETWEEN (CONVERT(DATETIME, '2003-08-01 00:00:00', 102)) AND (CONVERT(DATETIME, '2006-12-31 00:00:00', 102)))
group by contact1.company
This query works with no problem.
I now want to convert the seconds into HH:MM:SS format and get it to work.
I tried the following:
SELECT CONTACT1.COMPANY,
convert(datetime, (sum(datediff(ss,'00:00:00',CONTHIST.DURATION))), 14)
FROM CONTACT1 INNER JOIN CONTHIST ON CONTACT1.ACCOUNTNO = CONTHIST.ACCOUNTNO and isdate(CONTHIST.DURATION)=1
where (CONTHIST.RESULTCODE LIKE 'CT%') AND
(CONTHIST.ONDATE BETWEEN (CONVERT(DATETIME, '2003-08-01 00:00:00', 102)) AND (CONVERT(DATETIME, '2006-12-31 00:00:00', 102)))
group by contact1.company
but it gives me a result like 1941-11-22 00:00:00.000
Can anyone help write a query that will give me what I want?
Thanks,
Paul
March 3, 2006 at 12:34 pm
I tried this:
create table MyTime (mytime varchar(8))
insert into mytime
values('11:15:42')
select sum(datediff(ss,'0:0:0',mytime.mytime)) as seconds
from mytime
and that did not work. I found problems to convert to datetime from char or varchar, can you change the initial column to datetime or int?
March 3, 2006 at 12:44 pm
How about:
dateadd(s,sum(datediff(ss,'0:0:0',CONTHIST.DURATION)) ,0)
Mark
March 3, 2006 at 1:14 pm
Try something like this:
declare @CONTHIST table (Company varchar(4), Duration varchar(8))
insert @CONTHIST values ('aaa', '01:00:00')
insert @CONTHIST values ('aaa', '02:00:01')
insert @CONTHIST values ('aaa', '00:01:00')
insert @CONTHIST values ('bbb', '00:01:00')
--works if total is < 1 day
select a.company, convert(varchar, dateadd(ss, a.seconds, '01 Jan 2005' ), 108)
from (SELECT Company,
sum(datediff(ss,'0:0:0', Duration)) as seconds
FROM @CONTHIST
group by Company) a
--works for all cases
select a.Company, right('0'+cast(a.Seconds/(60*60) as varchar), 2) + ':' + right('0'+cast((a.Seconds-(a.Seconds/(60*60))*60*60)/60 as varchar), 2) + ':' + right('0'+cast(a.Seconds%60 as varchar), 2)
from (SELECT Company,
sum(datediff(ss,'0:0:0', Duration)) as seconds
FROM @CONTHIST
group by Company) a
March 3, 2006 at 1:22 pm
or check this :
create table MyTime (mytime datetime)
insert into mytime
values('12:25:35') ---and couple more values in the same format
select * from MyTime
declare @X int
set @x = (select sum(datediff(ss,'0:0:0',mytime)) from mytime)
select CONVERT(varchar,@X/3600) + ':'+ convert(varchar,(@x-(@X/3600)*3600)/60)+
+':'+ convert(varchar,@X -((@X/3600)*3600 )- ((@x-(@X/3600)*3600)/60)*60)
I think it's something similar
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply