Query to show Account Specific Category, Sub category and Item (MSSQL & PGSQL)

Query to show Account Specific Category, Sub category and Item (MSSQL & PGSQL)

Tested in Build PGSQL (14300) or MSSQL (14306)

SELECT accountdefinition.org_name "Account Name", CategoryDefinition.CATEGORYNAME "Category Name", SubCategoryDefinition.NAME "Sub Category Name" ,ItemDefinition.NAME"Item Name"   FROM CategoryDefinition left join SubCategoryDefinition on  SubCategoryDefinition.CATEGORYID=CategoryDefinition.CATEGORYID  left join ItemDefinition on ItemDefinition.SUBCATEGORYID = SubCategoryDefinition.SUBCATEGORYID left join categoryaccountmapping on categorydefinition.categoryid = categoryaccountmapping.CATEGORYID left join accountdefinition on categoryaccountmapping.accountid=accountdefinition.org_id group by CategoryDefinition.CATEGORYNAME,SubCategoryDefinition.NAME,ItemDefinition.NAME, accountdefinition.org_name order by 1
                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Query to show Category, Sub category and Item (MSSQL & PGSQL)

                      Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: Excute the below query under Report > New Report > Query Report > Copy, Paste the below and Run the query. SELECT CategoryDefinition.CATEGORYNAME "Category Name", ...
                    • Query to show Average response time for Category (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...
                    • Query to show Problems, its associated incidents and change_ (MSSQL)

                      Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...
                    • Query to show users of Organization Roles for each account (MSSQL & PGSQL)

                      T ested in Build PGSQL (14300) or MSSQL (14306) select ad.org_name "Account Name", org.orgrolename "ORG Role Name", aau.first_name "User" from orgroles org left join roletousermapping rtu ON org.orgroleid=rtu.roleid LEFT JOIN roletouseraccountmapping ...
                    • Query to show ticket aging - PGSQL

                      Last tested on 14500 Database: PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item", ...