Request group assignation time query
Can you please assist to make this query usable under SD+ 9307?
SELECT "wo"."WORKORDERID"
AS "Request ID",
"icd"."NAME" AS "Item",
"wo"."TITLE" AS "Subject",
"rtdef"."NAME" AS "Request Type",
"std"."STATUSNAME" AS "Request Status",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.createdtime/1000) + 3600),'1970-01-01 00:00:00') "Created Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((woh.OPERATIONTIME/1000) + 3600),'1970-01-01 00:00:00') "Assignation Time",
(woh.OPERATIONTIME - WO.CREATEDTIME)/1000 "Time to Assign",
--CONCAT(CONVERT(varchar(6),((woh.OPERATIONTIME - WO.CREATEDTIME)/1000)/3600),':',CONVERT(varchar(2),(((woh.OPERATIONTIME - WO.CREATEDTIME)/1000) % 36000)/60),':',CONVERT(varchar(2),((woh.OPERATIONTIME - WO.CREATEDTIME)/1000)%60)) "Time to Assign",
"aau"."FIRST_NAME" AS "Requester",
"ti"."FIRST_NAME" AS "Technician",
("wo"."TIMESPENTONREQ"/1000) AS "Time Elapsed",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.RESOLVEDTIME/1000) + 3600),'1970-01-01 00:00:00') "Resolved Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.COMPLETEDTIME/1000) + 3600),'1970-01-01 00:00:00') "Completed Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.RESPONDEDTIME/1000) + 3600),'1970-01-01 00:00:00') "Responded Date",
"cd"."CATEGORYNAME" AS "Category",
"scd"."NAME" AS "Subcategory",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wos.LAST_TECH_UPDATE/1000) + 3600),'1970-01-01 00:00:00') "Last Update Time"
FROM "WorkOrder" "wo" LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID"
LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
LEFT JOIN "CategoryDefinition" "cd" ON "wos"."CATEGORYID"="cd"."CATEGORYID"
LEFT JOIN "SubCategoryDefinition" "scd" ON "wos"."SUBCATEGORYID"="scd"."SUBCATEGORYID"
LEFT JOIN "ItemDefinition" "icd" ON "wos"."ITEMID"="icd"."ITEMID"
LEFT JOIN "SDUser" "td" ON "wos"."OWNERID"="td"."USERID"
LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID"
LEFT JOIN "RequestTypeDefinition" "rtdef" ON "wos"."REQUESTTYPEID"="rtdef"."REQUESTTYPEID"
LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID"
LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID"
LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID"
LEFT JOIN WorkOrderHistory woh ON woh.WORKORDERID=wo.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
LEFT JOIN QueueDefinition qdc ON cast (qdc.QUEUEID as varchar) = wohd.CURRENT_VALUE
WHERE "qd"."QUEUENAME" IN ('Dcompras','TecWeb')
AND QDC.QUEUENAME IN ('Dcompras','Tecweb')
AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.createdtime/1000) + 3600),'1970-01-01 00:00:00')
BETWEEN convert(varchar,'2017-10-01 00:00:00',21) and convert(varchar,'2017-10-31 23:59:59',21)
Order by "wo"."WORKORDERID"
It works under sql but not directly in SD+
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(Unknown Source)
at com.adventnet.servicedesk.reports.utils.ReportUtil.parseDateColumns(ReportUtil.java:2585)
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7974)
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7835)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1726)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:2071)
at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:196)
at com.adventnet.servicedesk.reports.action.ReportHandler.execute(ReportHandler.java:60)
at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:305)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.manageengine.servicedesk.common.filter.ResetThreadLocal.doFilter(ResetThreadLocal.java:26)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.adventnet.servicedesk.filter.EncodingFilter.doFilter(EncodingFilter.java:225)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:350)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.adventnet.servicedesk.filter.RememberMe.doFilter(RememberMe.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.adventnet.servicedesk.authentication.NTLMV2CredentialAssociationFilter.doFilter(NTLMV2CredentialAssociationFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.adventnet.servicedesk.authentication.NTLMV2Filter.doFilter(NTLMV2Filter.java:198)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:70)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:581)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at com.adventnet.authentication.CredentialAssociation.invoke(CredentialAssociation.java:78)
at com.adventnet.servicedesk.tools.AuthenticateNtlm.invoke(AuthenticateNtlm.java:175)
at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:336)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Thanks
SELECT "wo"."WORKORDERID"
AS "Request ID",
"icd"."NAME" AS "Item",
"wo"."TITLE" AS "Subject",
"rtdef"."NAME" AS "Request Type",
"std"."STATUSNAME" AS "Request Status",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.createdtime/1000) + 3600),'1970-01-01 00:00:00') "Created Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((woh.OPERATIONTIME/1000) + 3600),'1970-01-01 00:00:00') "Assignation Time",
(woh.OPERATIONTIME - WO.CREATEDTIME)/1000 "Time to Assign",
--CONCAT(CONVERT(varchar(6),((woh.OPERATIONTIME - WO.CREATEDTIME)/1000)/3600),':',CONVERT(varchar(2),(((woh.OPERATIONTIME - WO.CREATEDTIME)/1000) % 36000)/60),':',CONVERT(varchar(2),((woh.OPERATIONTIME - WO.CREATEDTIME)/1000)%60)) "Time to Assign",
"aau"."FIRST_NAME" AS "Requester",
"ti"."FIRST_NAME" AS "Technician",
("wo"."TIMESPENTONREQ"/1000) AS "Time Elapsed",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.RESOLVEDTIME/1000) + 3600),'1970-01-01 00:00:00') "Resolved Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.COMPLETEDTIME/1000) + 3600),'1970-01-01 00:00:00') "Completed Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.RESPONDEDTIME/1000) + 3600),'1970-01-01 00:00:00') "Responded Date",
"cd"."CATEGORYNAME" AS "Category",
"scd"."NAME" AS "Subcategory",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wos.LAST_TECH_UPDATE/1000) + 3600),'1970-01-01 00:00:00') "Last Update Time"
FROM "WorkOrder" "wo" LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID"
LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
LEFT JOIN "CategoryDefinition" "cd" ON "wos"."CATEGORYID"="cd"."CATEGORYID"
LEFT JOIN "SubCategoryDefinition" "scd" ON "wos"."SUBCATEGORYID"="scd"."SUBCATEGORYID"
LEFT JOIN "ItemDefinition" "icd" ON "wos"."ITEMID"="icd"."ITEMID"
LEFT JOIN "SDUser" "td" ON "wos"."OWNERID"="td"."USERID"
LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID"
LEFT JOIN "RequestTypeDefinition" "rtdef" ON "wos"."REQUESTTYPEID"="rtdef"."REQUESTTYPEID"
LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID"
LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID"
LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID"
LEFT JOIN WorkOrderHistory woh ON woh.WORKORDERID=wo.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
LEFT JOIN QueueDefinition qdc ON cast (qdc.QUEUEID as varchar) = wohd.CURRENT_VALUE
WHERE "qd"."QUEUENAME" IN ('Dcompras','TecWeb')
AND QDC.QUEUENAME IN ('Dcompras','Tecweb')
AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((wo.createdtime/1000) + 3600),'1970-01-01 00:00:00')
BETWEEN convert(varchar,'2017-10-01 00:00:00',21) and convert(varchar,'2017-10-31 23:59:59',21)
Order by "wo"."WORKORDERID"
It works under sql but not directly in SD+
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(Unknown Source)
at com.adventnet.servicedesk.reports.utils.ReportUtil.parseDateColumns(ReportUtil.java:2585)
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7974)
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7835)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1726)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:2071)
at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:196)
at com.adventnet.servicedesk.reports.action.ReportHandler.execute(ReportHandler.java:60)
at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:305)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.manageengine.servicedesk.common.filter.ResetThreadLocal.doFilter(ResetThreadLocal.java:26)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.adventnet.servicedesk.filter.EncodingFilter.doFilter(EncodingFilter.java:225)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:350)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.adventnet.servicedesk.filter.RememberMe.doFilter(RememberMe.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.adventnet.servicedesk.authentication.NTLMV2CredentialAssociationFilter.doFilter(NTLMV2CredentialAssociationFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.adventnet.servicedesk.authentication.NTLMV2Filter.doFilter(NTLMV2Filter.java:198)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:70)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:581)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at com.adventnet.authentication.CredentialAssociation.invoke(CredentialAssociation.java:78)
at com.adventnet.servicedesk.tools.AuthenticateNtlm.invoke(AuthenticateNtlm.java:175)
at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:336)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Thanks