August 11, 2005 at 9:51 am
HI All,
I am trying to run a query on my CRM database.
SELECT CONTACT1.COMPANY, SUM(convert(int,CONTHIST.DURATION))
FROM CONTACT1 INNER JOIN CONTHIST ON CONTACT1.ACCOUNTNO = CONTHIST.ACCOUNTNO
WHERE (CONTHIST.RESULTCODE LIKE 'CT%') AND (CONTHIST.ONDATE < CONVERT(DATETIME, '2005-12-31 00:00:00', 102)) AND (CONTHIST.ONDATE > CONVERT(DATETIME, '2003-8-01 00:00:00', 102)) and conthist.duration is not null
GROUP BY CONTACT1.COMPANY
All of the values in my conthist.duration field are formatted as a time 00:00:00
Schema:
COMPANY (varchar(40),Null)
Duration (varchar(8),Null)
When I run it I get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '01:45:00' to a column of data type int.
Anyone have any ideas why this won't run?
Paul
August 11, 2005 at 10:00 am
simpli because '01:45:00' is not an int
you can convert to a datetime but not int
you can get sum os seconds actully and you need to transform it back to time format after
SELECT CONTACT1.COMPANY, SUM(datediff(second,0,convert(datetime,CONTHIST.DURATION)))
FROM CONTACT1 INNER JOIN CONTHIST ON CONTACT1.ACCOUNTNO = CONTHIST.ACCOUNTNO
WHERE (CONTHIST.RESULTCODE LIKE 'CT%') AND (CONTHIST.ONDATE < CONVERT(DATETIME, '2005-12-31 00:00:00', 102)) AND (CONTHIST.ONDATE > CONVERT(DATETIME, '2003-8-01 00:00:00', 102)) and conthist.duration is not null
GROUP BY CONTACT1.COMPANY
Vasc
August 11, 2005 at 3:57 pm
If I run the following:
SELECT CONTACT1.COMPANY, SUM(convert(datetime,CONTHIST.DURATION))
FROM CONTACT1 INNER JOIN CONTHIST ON CONTACT1.ACCOUNTNO = CONTHIST.ACCOUNTNO
WHERE (CONTHIST.RESULTCODE LIKE 'CT%') AND (CONTHIST.ONDATE CONVERT(DATETIME, '2003-8-01 00:00:00', 102))
GROUP BY CONTACT1.COMPANY
Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a datetime data type as an argument.
How would I get a sum of seconds and convert it back to time?
August 12, 2005 at 4:58 pm
The code below will produce identical sums for each company but it illustrates how to sum(seconds). Mess around with the variables if you want or manually manipulate the datetime values in the table or even delete a few rows to make the totals come out different.
Try this:
declare @interval Int,
@stime datetime,
@etime datetime,
@delay char(9)
set @interval = 1
create table #times (uid int Identity (1,1) not null,
Company varchar(20) null,
StartDate datetime null,
EndDate datetime null) ON 'PRIMARY'
SET @delay = '000:00:02' -- 2 second delay
while @interval < 21
BEGIN
Set @stime = getdate()
waitfor delay @delay -- wait x seconds...
Set @etime = getdate() -- get a later timestamp
INSERT INTO #Times
(Company, StartDate, EndDate)
VALUES('Company-' + ltrim(rtrim(str(@interval))), @stime, @etime)
Set @stime = DateAdd(ss, 1, getdate())
Set @Interval = @Interval + 1
END
-- now make some extra rows... for other days.. to simulate history
INSERT INTO #times
(Company, StartDate, EndDate)
SELECT
Company, StartDate = DateAdd(dd,1,StartDate), EndDate = DateAdd(dd,1,EndDate)
FROM #times
UNION ALL
SELECT
Company, StartDate = DateAdd(dd,2,StartDate), EndDate = DateAdd(dd,2,EndDate)
FROM #times
-- now add them up...
select
dt.Company,
TotalDuration = SUM(dt.Duration)
from (
select
Company,
StartDate,
EndDate,
Duration = DateDiff(ss,StartDate, EndDate)
from #times
) dt
Group by dt.Company
order by dt.Company
hope this helps...
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply