Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unhappy Unanswered: Attempting to do a Max of a Sum

    Since I cannot do an aggregate within an aggregate, I attempted to put a sub-query within the Max, but I am getting a syntax error. With the exception of the last statement on the where clause of the sub-query (and csq1.csqname = csq.csqname) I know that the sub-query will run by it self. I have other summarized fields that will be part of the main query (not included here to keep it simple).

    select csqname
    , max (select sum(acd1.talkTime+acd1.holdTime+acd1.workTime)
    from contactservicequeue csq1
    join contactqueuedetail cqd1 on csq1.recordid = cqd1.targetid
    join contactcalldetail ccd1 on cqd1.sessionid = ccd1.sessionid
    and ccd1.nodeid = cqd1.nodeid
    and ccd1.sessionseqnum = cqd1.sessionseqnum
    join agentconnectiondetail acd1 on acd1.sessionid = ccd1.sessionid
    and acd1.sessionseqnum = ccd1.sessionseqnum
    and acd1.nodeid = ccd1.nodeid
    where cqd1.targettype = 0
    and (ccd1.startdatetime+ INTERVAL(-8:00) HOUR TO MINUTE ) >= mdy(11,10,2014)
    and (ccd1.startdatetime+ INTERVAL(-8:00) HOUR TO MINUTE ) < mdy(11,11,2014)
    and csq1.csqname = csq.csqname)
    from
    contactservicequeue csq
    join contactqueuedetail cqd on csq.recordid = cqd.targetid
    join contactcalldetail ccd on cqd.sessionid = ccd.sessionid
    and ccd.nodeid = cqd.nodeid
    and ccd.sessionseqnum = cqd.sessionseqnum
    join agentconnectiondetail acd on acd.sessionid = ccd.sessionid
    and acd.sessionseqnum = ccd.sessionseqnum
    and acd.nodeid = ccd.nodeid
    where cqd.targettype = 0
    and (ccd.startdatetime+ INTERVAL(-8:00) HOUR TO MINUTE ) >= mdy(11,10,2014)
    and (ccd.startdatetime+ INTERVAL(-8:00) HOUR TO MINUTE ) < mdy(11,11,2014)
    group by csqname

    Any suggestions are welcomed.

  2. #2
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    I'm not sure that I get you right, but I think you mean this one:
    Code:
    select sub.csqname, max(sub.subsum)
    from (select csqname, sum(acd1.talkTime+acd1.holdTime+acd1.workTime) subsum
          from contactservicequeue csq1
          join contactqueuedetail cqd1 on csq1.recordid = cqd1.targetid
          join contactcalldetail ccd1 on cqd1.sessionid = ccd1.sessionid
          and ccd1.nodeid = cqd1.nodeid
          and ccd1.sessionseqnum = cqd1.sessionseqnum
          join agentconnectiondetail acd1 on acd1.sessionid = ccd1.sessionid
          and acd1.sessionseqnum = ccd1.sessionseqnum
          and acd1.nodeid = ccd1.nodeid
          where cqd1.targettype = 0
          and (ccd1.startdatetime+ INTERVAL(-8:00) HOUR TO MINUTE ) >= mdy(11,10,2014) 
          and (ccd1.startdatetime+ INTERVAL(-8:00) HOUR TO MINUTE ) < mdy(11,11,2014) 
          and csq1.csqname = csq.csqname
          group by csqname) sub
    WilhelmW

  3. #3
    Join Date
    Mar 2010
    Posts
    4

    Thanks.

    That should work.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •