Sum and Convert issue, query not working

  • 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

  • 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


    Kindest Regards,

    Vasc

  • 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?

  • 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