{"action":"opened","issue":{"url":"https://api.github.com/repos/jOOQ/jOOQ/issues/8134","repository_url":"https://api.github.com/repos/jOOQ/jOOQ","labels_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/8134/labels{/name}","comments_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/8134/comments","events_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/8134/events","html_url":"https://github.com/jOOQ/jOOQ/issues/8134","id":392177634,"node_id":"MDU6SXNzdWUzOTIxNzc2MzQ=","number":8134,"title":"Error translate query sql server to informix query","user":{"login":"thiago899","id":37217847,"node_id":"MDQ6VXNlcjM3MjE3ODQ3","avatar_url":"https://avatars1.githubusercontent.com/u/37217847?v=4","gravatar_id":"","url":"https://api.github.com/users/thiago899","html_url":"https://github.com/thiago899","followers_url":"https://api.github.com/users/thiago899/followers","following_url":"https://api.github.com/users/thiago899/following{/other_user}","gists_url":"https://api.github.com/users/thiago899/gists{/gist_id}","starred_url":"https://api.github.com/users/thiago899/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thiago899/subscriptions","organizations_url":"https://api.github.com/users/thiago899/orgs","repos_url":"https://api.github.com/users/thiago899/repos","events_url":"https://api.github.com/users/thiago899/events{/privacy}","received_events_url":"https://api.github.com/users/thiago899/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-12-18T14:20:22Z","updated_at":"2018-12-18T14:20:22Z","closed_at":null,"author_association":"NONE","body":"DECLARE @STARTDATE datetime\r\nDECLARE @ENDDATE datetime\r\nDECLARE @STARTHOUR INT\r\nDECLARE @ENDHOUR INT\r\nDECLARE @CSQS VARCHAR(50)\r\n\r\nSET @STARTDATE = '{startDate}'\r\nSET @ENDDATE = '{endDate}'\r\nSET @STARTHOUR = '{startHour}'\r\nSET @ENDHOUR = '{endHour}'\r\nSET @CSQS = '{csqs}'\r\n\r\n\r\nIF OBJECT_ID('tempdb.dbo.#HH', 'U') IS NOT NULL BEGIN DROP TABLE #HH END\r\nIF OBJECT_ID('tempdb.dbo.#loggedin', 'U') IS NOT NULL BEGIN DROP TABLE #loggedin END\r\nIF OBJECT_ID('tempdb.dbo.#agents', 'U') IS NOT NULL BEGIN DROP TABLE #agents END\r\nIF OBJECT_ID('tempdb.dbo.#queue', 'U') IS NOT NULL BEGIN DROP TABLE #queue END\r\nIF OBJECT_ID('tempdb.dbo.#queueAgent', 'U') IS NOT NULL BEGIN DROP TABLE #queueAgent END\r\nIF OBJECT_ID('tempdb.dbo.#hoursummary', 'U') IS NOT NULL BEGIN DROP TABLE #hoursummary END\r\nIF OBJECT_ID('tempdb.dbo.#hoursummaryAgent', 'U') IS NOT NULL BEGIN DROP TABLE #hoursummaryAgent END\r\nIF OBJECT_ID('tempdb.dbo.#hourdata', 'U') IS NOT NULL BEGIN DROP TABLE #hourdata END\r\nIF OBJECT_ID('tempdb.dbo.#hourdataAgent', 'U') IS NOT NULL BEGIN DROP TABLE #hourdataAgent END\r\n\r\nselect n into #HH from (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 \r\n\t\tFROM sys.all_objects) nums where n <= 23\r\n\r\nselect\r\n\t#HH.n as \"Hour\",\r\n\tsum(CASE when datepart(HH, asd.eventdatetime) between #HH.n and #HH.n+1 then 1 else 0 end) LoggedIn,\r\n\tsum(CASE when (datepart(HH, asd.eventdatetime) between #HH.n and #HH.n+1 and asd.eventtype between 5 and 6) then 1 else 0 end) Working\r\ninto #loggedin\r\nfrom \r\n\t\"resource\" r\r\n\tjoin agentstatedetail asd on r.resourceid = asd.agentid and asd.eventtype <> 7\r\n\tjoin #HH on #HH.n = datepart(HH,asd.eventdatetime)\r\nwhere\r\n\tconvert(date,asd.eventdatetime,105) between @STARTDATE and @ENDDATE\r\n\tand #HH.n between @STARTHOUR and @ENDHOUR\r\ngroup by \r\n\t#HH.n\r\norder by 1, 2\r\n\r\n\r\nselect\r\n\t#HH.n as \"Hour\",\r\n\tsum(case when cqd.queuetime > 20 then 1 else 0 end) as Gt20Queue,\r\n\tmax(cqd.queueorder) as MaxQueueSize,\r\n\tmax(cqd.queuetime) as MaxQueueTime,\r\n\tsum(case when cqd.disposition = 1 then 1 else 0 end) as TotalAbandoned,\r\n\tsum(case when ccd.contactdisposition = 2 then 1 else 0 end) as TotalHandled\r\ninto #queue\r\nfrom\r\n\t\"contactservicequeue\" csq\r\n\tjoin contactqueuedetail cqd on csq.contactservicequeueid = cqd.targetid and cqd.targettype = 0\r\n\tjoin contactcalldetail ccd on cqd.sessionid = ccd.sessionid\r\n\tjoin #HH on #HH.n = datepart(HH,ccd.startdatetime)\r\nwhere\r\n\tconvert(date,ccd.startdatetime,105) between @STARTDATE and @ENDDATE\r\n\tand #HH.n between @STARTHOUR and @ENDHOUR\r\n\tand (','+ @CSQS+',') LIKE '%,' + CAST(csq.contactservicequeueid as VARCHAR) + ',%'\r\ngroup by\r\n\t#HH.n\r\norder by 1,2\r\n\r\nselect\r\n\t#HH.n as \"Hour\",\r\n\tavg(acd.talktime + acd.worktime) as AvgDuration,\r\n\tstdevp(acd.talktime + acd.worktime) as stdDev,\r\n\tmin(acd.talktime + acd.worktime) as MinDuration,\r\n\tmax(acd.talktime + acd.worktime) as MaxDuration\r\ninto #hoursummary\r\nfrom\r\n\tcontactqueuedetail cqd\r\n\tjoin agentconnectiondetail acd on cqd.sessionid = acd.sessionid\r\n\tjoin #HH on #HH.n = datepart(HH,acd.startdatetime)\r\nwhere\r\n\tconvert(date,acd.startdatetime,105) between @STARTDATE and @ENDDATE\r\n\tand #HH.n between @STARTHOUR and @ENDHOUR\r\n\tand (','+ @CSQS+',') LIKE '%,' + CAST(cqd.targetid as VARCHAR) + ',%' and cqd.targettype = 0\r\ngroup by\r\n\t#HH.n\r\norder by 1,2\r\n\r\nselect\r\n\t#HH.n as \"Hour\",\r\n\ths.AvgDuration,\r\n\ths.stdDev,\r\n\ths.MaxDuration,\r\n\ths.MinDuration,\r\n\tsum(case when (acd.talktime + acd.worktime) >= hs.AvgDuration then 1 else 0 end) as GtAverage,\r\n\tsum(case when (acd.talktime + acd.worktime) < hs.AvgDuration then 1 else 0 end) as LtAverage\r\ninto #hourdata\r\nfrom\r\n\tcontactqueuedetail cqd\r\n\tjoin agentconnectiondetail acd on cqd.sessionid = acd.sessionid and cqd.disposition = 2\r\n\tjoin #HH on #HH.n = datepart(HH,acd.startdatetime)\r\n\tjoin #hoursummary hs on #HH.n = hs.\"Hour\"\r\nwhere\r\n\tconvert(date,acd.startdatetime,105) between @STARTDATE and @ENDDATE\r\n\tand #HH.n between @STARTHOUR and @ENDHOUR\r\n\tand (','+ @CSQS+',') LIKE '%,' + CAST(cqd.targetid as VARCHAR) + ',%' and cqd.targettype = 0\r\ngroup by\r\n\t#HH.n,\r\n\ths.AvgDuration,\r\n\ths.stdDev,\r\n\ths.MaxDuration,\r\n\ths.MinDuration\t\r\norder by 1,2\r\n\r\n\r\n-- Query Part Agents\r\n\r\nselect\r\n\t#HH.n as \"Hour\",\r\n\tr.resourcename as agent,\r\n\tr.resourceid,\r\n\tavg(acd.talktime + acd.worktime) as AvgDuration,\r\n\tstdevp(acd.talktime + acd.worktime) as stdDev,\r\n\tmin(acd.talktime + acd.worktime) as MinDuration,\r\n\tmax(acd.talktime + acd.worktime) as MaxDuration\r\ninto #hoursummaryAgent\r\nfrom\r\n\t[resource] r\r\n\tjoin agentconnectiondetail acd on r.resourceid = acd.resourceid\r\n    join resourceskillmapping skm on r.resourceskillmapid = skm.resourceskillmapid\r\n    join skill s on skm.skillid = s.skillid\r\n    join skillgroup sg on s.skillid = sg.skillid\r\n    join contactservicequeue csq on sg.skillgroupid = csq.skillgroupid\r\n\tjoin contactqueuedetail cqd on csq.recordid = cqd.targetid and cqd.targettype = 0\r\n\tjoin #HH on #HH.n = datepart(HH,acd.startdatetime)\r\nwhere\r\n\tconvert(date,acd.startdatetime,105) between @STARTDATE and @ENDDATE\r\n\tand #HH.n between @STARTHOUR and @ENDHOUR\r\n\tand (','+ @CSQS+',') LIKE '%,' + CAST(cqd.targetid as VARCHAR) + ',%' and cqd.targettype = 0\r\ngroup by\r\n\t#HH.n,\r\n\tr.resourceid,\r\n\tr.resourcename\r\norder by 1,2\r\n\r\nselect\r\n\t#HH.n as \"Hour\",\r\n\thsa.agent,\r\n\thsa.resourceid,\r\n\thsa.AvgDuration,\r\n\thsa.stdDev,\r\n\thsa.MaxDuration,\r\n\thsa.MinDuration,\r\n\tsum(case when (acd.talktime + acd.worktime) >= hsa.AvgDuration then 1 else 0 end) as GtAverage,\r\n\tsum(case when (acd.talktime + acd.worktime) < hsa.AvgDuration then 1 else 0 end) as LtAverage,\r\n\tsum(case when acd.talktime > 0 then 1 else 0 end) as TotalHandled\r\ninto #hourdataAgent\r\nfrom\r\n\t[resource] r\r\n\tjoin agentconnectiondetail acd on r.resourceid = acd.resourceid\r\n    join resourceskillmapping skm on r.resourceskillmapid = skm.resourceskillmapid\r\n    join skill s on skm.skillid = s.skillid\r\n    join skillgroup sg on s.skillid = sg.skillid\r\n    join contactservicequeue csq on sg.skillgroupid = csq.skillgroupid\r\n\tjoin contactqueuedetail cqd on acd.sessionid = cqd.sessionid and csq.recordid = cqd.targetid and cqd.targettype = 0\r\n\tjoin contactcalldetail ccd on cqd.sessionid = ccd.sessionid\r\n\tjoin #HH on #HH.n = datepart(HH,acd.startdatetime)\r\n\tjoin #hoursummaryAgent hsa on r.resourceid = hsa.resourceid\tand #HH.n = hsa.\"Hour\"\r\nwhere\r\n\tconvert(date,acd.startdatetime,105) between @STARTDATE and @ENDDATE\r\n\tand #HH.n between @STARTHOUR and @ENDHOUR\r\n\tand (','+ @CSQS+',') LIKE '%,' + CAST(cqd.targetid as VARCHAR) + ',%' and cqd.targettype = 0\r\ngroup by\r\n\t#HH.n,\r\n\thsa.resourceid,\r\n\thsa.agent,\r\n\thsa.AvgDuration,\r\n\thsa.stdDev,\r\n\thsa.MaxDuration,\r\n\thsa.MinDuration\t\r\norder by 1,2\r\n\r\n-- Select all sub-queries\r\n\r\nselect \r\n\tl.\"Hour\",\r\n\tcoalesce(q.Gt20Queue,0) as Gt20Queue,\r\n\tcoalesce(q.MaxQueueSize,0) as MaxQueueSize,\r\n\tcoalesce(q.MaxQueueTime,0) as MaxQueueTime,\r\n\tcoalesce(l.LoggedIn,0) as LoggedIn,\r\n\tcoalesce(l.Working,0) as Working,\r\n\tcoalesce(hd.LtAverage,0) as LtAverage,\r\n\tcoalesce(hd.MinDuration,0)as MinDuration,\r\n\tcoalesce(hd.AvgDuration,0)as AvgDuration,\r\n\tcoalesce(hd.MaxDuration,0)as MaxDuration,\r\n\tcoalesce(hd.stdDev,0)as stdDev,\r\n\tcoalesce(hd.GtAverage,0)as GtAverage,\r\n\tcoalesce(q.TotalHandled,0)as TotalHandled,\r\n\tcoalesce(q.TotalAbandoned,0)as TotalAbandoned,\r\n\thda.agent as agent,\r\n\tcoalesce(hda.resourceid,0),\r\n\tcoalesce(hda.AvgDuration,0)as agent_AvgDuration,\r\n\tcoalesce(hda.GtAverage,0)as agent_GtAverage,\r\n\tcoalesce(hda.LtAverage,0)as agent_LtAverage,\r\n\tcoalesce(hda.MaxDuration,0)as agent_MaxDuration,\r\n\tcoalesce(hda.MinDuration,0)as agent_MinDuration,\r\n\tcoalesce(hda.stdDev,0)as agent_stdDev,\r\n\tcoalesce(hda.TotalHandled,0)as agent_TotalHandled\r\nfrom\r\n\t#loggedin l\r\n\tjoin #queue q on l.\"Hour\" = q.\"Hour\"\r\n\tjoin #hourdata hd on l.\"Hour\" = hd.\"Hour\"\r\n\tjoin #hourdataAgent hda on l.\"Hour\" = hda.\"Hour\"\r\norder by\r\n\tl.\"Hour\",\r\n\thda.agent asc"}}
{"actor":{"display_login":"thiago899"}}