Ageing Reports for Support Groups (MSSQL)

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. 
ParameterDescription
intervalRequired. The part to return. Can be one of the following values:
  • year, yyyy, yy = Year
  • quarter, qq, q = Quarter
  • month, mm, m = month
  • dayofyear = Day of the year
  • day, dy, y = Day
  • week, ww, wk = Week
  • weekday, dw, w = Weekday
  • hour, hh = hour
  • minute, mi, n = Minute
  • second, ss, s = Second
  • millisecond, ms = Millisecond


      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Support Groups

                      This Report is used to export the list of Support groups configured in the application with the complete details. MSSQL SELECT qd.QUEUENAME "Support group", ci.DESCRIPTION "Description", su.first_name "Owned By", STUFF( (SELECT ',' + au.first_name ...

                    • 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", ...

                      Related Products