Ageing Reports for Support Groups (MSSQL)
Tested in build MSSQL (14306)
SELECT QUEUEDEFINITION.QUEUENAME,COUNT(WORKORDER.WORKORDERID) 'Total Pending Count',
COUNT(CASE WHEN DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) <= 2 THEN 1 ELSE NULL END) '0 - 2 hours',
COUNT(CASE when DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) <= 5 and DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) > 2 THEN 1 ELSE NULL END) '2 - 5 hours',
COUNT(CASE when DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) <= 10 and DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) > 5 THEN 1 ELSE NULL END) '5 - 10 hours',
COUNT(CASE when DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) <= 15 and DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) > 10 THEN 1 ELSE NULL END) '10 - 15 hours',
COUNT(CASE when DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) <= 30 and DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) > 15 THEN 1 ELSE NULL END) '15 - 30 hours',
COUNT(CASE when DATEDIFF(y, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +((WORKORDER.createdtime)/1000),'1970-01-01 00:00:00'),GETDATE()) > 30 THEN 1 ELSE NULL END) 'More than 30 hours' FROM WORKORDER
INNER JOIN WORKORDERSTATES on WORKORDERSTATES.WORKORDERID = WORKORDER.WORKORDERID LEFT JOIN STATUSDEFINITION on STATUSDEFINITION.STATUSID = WORKORDERSTATES.STATUSID LEFT JOIN WORKORDER_QUEUE ON WORKORDER.WORKORDERID = WORKORDER_QUEUE.WORKORDERID LEFT JOIN QUEUEDEFINITION ON WORKORDER_QUEUE.QUEUEID=QUEUEDEFINITION.QUEUEID WHERE WORKORDERSTATES.STATUSID IN ( Select STATUSID from StatusDefinition where ISPENDING=1) GROUP BY QUEUEDEFINITION.QUEUENAME
ORDER BY QUEUEDEFINITION.QUEUENAME
You can replace the highlighted y with the below interval parameters.
| Parameter | Description |
|---|---|
| interval | Required. The part to return. Can be one of the following values:
|
New to ADSelfService Plus?
Related Articles
Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...Query to list support groups along with its account , site name , group status ( MSSQL & PGSQL )
Tested in build PGSQL (14300) and MSSQL (14306) select qd.queuename "Group Name",qe.email "Email",sdo.name "Site Name",adef.org_name "Account Name" from queuedefinition qd LEFT JOIN queue_email qe ON qd.queueid=qe.queueid LEFT JOIN sitedefinition ...Query to show support groups and its individual custom attributes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT ci.CINAME AS "Support Group Name", su.first_name "Owned By", ci.DESCRIPTION AS "Description",ia.attributename "Additional Attributes Name",ia.attributevalue "Additional Attributes ...Task related reports (MSSQL)
Tested in MSSQL build (14306) Sample Output and headers present in this report Goto reports -> New query report and executes the following SELECT taskdet.TASKID AS "Task ID", taskdet.MODULE AS "Module", taskprior.PRIORITYNAME AS "Priority", ...Support Groups configuration
1) The support groups that we are configuring in a template is always based on the sites. When we have more than one account assigned in the template, only the support groups added under “Default Settings” will get displayed in the template. Refer ...