Query to fetch Checklist Information

Query to fetch Checklist Information

DB Compatibility : PGSQL & MSSQL
Build Compatibility : Builds above 11107

PGSQL :

SELECT  wo.WORKORDERID AS "Request ID", 
aau.FIRST_NAME AS "Requester", 
wo.TITLE AS "Subject", 
cd.CATEGORYNAME AS "Category", 
scd.NAME AS "Subcategory", 
icd.NAME AS "Item", 
pd.PRIORITYNAME AS "Priority", 
qd.QUEUENAME AS "Group", 
std.STATUSNAME AS "Request Status",
dpt.DEPTNAME AS "Department", 
ti.FIRST_NAME AS "Technician", 
longtodate(wo.CREATEDTIME) AS "Created Time", 
longtodate(wo.COMPLETEDTIME) AS "Completed Time", 
rrs.RESOLUTION AS "Resolution", 
rtl.templatename as "Template Name",
mc.title as "Checklist name",
mc.status as "Checklist verified or not",
(SELECT string_agg(concat(mcl.fieldname , ' : ' , (case mcl.field_type when 'Numeric' then mcl.cl_long::varchar when 'Boolean' then mcl.cl_bool::varchar else mcl.cl_char end ) ), ' , ' ) FROM workordertochecklist wcl 
left join modulechecklist mc1 on wcl.checklistid=mc1.checklistid 
left join modulechecklistitem mcl on mc1.checklistid=mcl.checklistid 
where wcl.workorderid=wo.workorderid and mc1.checklistid = mc.checklistid) as "ChecklistData" FROM workorder wo 
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID 
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID 
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
LEFT JOIN StatusDefinition std ON wos.statusid=std.statusid 
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID 
LEFT JOIN RequestResolution rrs ON  wo.WORKORDERID=rrs.REQUESTID  
LEFT JOIN requesttemplate_list rtl ON wo.templateid = rtl.templateid
LEFT JOIN workordertochecklist wcl ON wo.WORKORDERID = wcl.WORKORDERID
left join modulechecklist mc on wcl.checklistid=mc.checklistid 
where  wo.ISPARENT='1'  and wo.createdtime >= <from_thisweek> and wo.createdtime <= <to_thisweek>


MSSQL :

SELECT  wo.WORKORDERID AS "Request ID", 
aau.FIRST_NAME AS "Requester", 
wo.TITLE AS "Subject", 
cd.CATEGORYNAME AS "Category", 
scd.NAME AS "Subcategory", 
icd.NAME AS "Item", 
pd.PRIORITYNAME AS "Priority", 
qd.QUEUENAME AS "Group", 
std.STATUSNAME AS "Request Status",
dpt.DEPTNAME AS "Department", 
ti.FIRST_NAME AS "Technician", 
longtodate(wo.CREATEDTIME) AS "Created Time", 
longtodate(wo.COMPLETEDTIME) AS "Completed Time", 
rrs.RESOLUTION AS "Resolution", 
rtl.templatename as "Template Name",
mc.title as "Checklist name",
mc.status as "Checlist verified or not",
cast(STUFF(( SELECT ',' + mcl.fieldname + ':' + (case mcl.field_type when 'Numeric' then cast(mcl.cl_long as varchar) when 'Boolean' then cast(mcl.cl_bool as varchar) else mcl.cl_char end ) FROM workordertochecklist wcl 
left join modulechecklist mc1 on wcl.checklistid=mc1.checklistid 
left join modulechecklistitem mcl on mc1.checklistid=mcl.checklistid 
where wcl.workorderid=wo.workorderid and mc1.checklistid = mc.checklistid 
        FOR XML PATH('')),1,1,'')  as  varchar(150)) as "ChecklistData" FROM workorder wo 
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID 
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID 
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
LEFT JOIN StatusDefinition std ON wos.statusid=std.statusid 
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID 
LEFT JOIN RequestResolution rrs ON  wo.WORKORDERID=rrs.REQUESTID  
LEFT JOIN requesttemplate_list rtl ON wo.templateid = rtl.templateid
LEFT JOIN workordertochecklist wcl ON wo.WORKORDERID = wcl.WORKORDERID
left join modulechecklist mc on wcl.checklistid=mc.checklistid 
where  wo.ISPARENT='1'  and wo.createdtime >= <from_thisweek> and wo.createdtime <= <to_thisweek>



In the above query, you can replace the <from_thisweek> and <to_thisweek> with the desired date filter. You can look for the available list of date filters here - Date Filter

                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Query Executor Tool for PostGres

                      This tool is designed to execute queries in the customer environment by connecting the database by reading the database configuration file. We need to enter the query that we require to execute in queryToExecute.txt file. We can enter multiple ...
                    • How to check System Info using WBEMTEST

                      There are issues where the Service Tag , Serial Number or other system information are not correct in the scan results.We can get BIOS & other setting from the wbemtest tool (Inbuild Microsoft WMI tool) and compare it with the results ...
                    • Query to fetch the templates

                      The query fetches all the fields with display name used across Request templates PQSQL: select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", rt.templatename "Template name" , sd.name "Service ...
                    • Query to fetch Task Worklog and related entity ID

                      DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 9400 SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskdet.MODULE AS "Module", wotask.WORKORDERID AS "Request ID", taskprob.PROBLEMID ...
                    • PowerShell script to fetch all the disposed assets based on portal specific

                      Steps to use the provided script: Ensure that the required modules are installed by executing the following commands: $ErrorActionPreference = "Stop" Install-Module -Name 'ImportExcel' -Force -AllowClobber Import the necessary modules by executing ...