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

          • Related Articles

          • 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 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 ...
          • Dynamic checklist based on field values

            Requirement: Based on the answers provided for resource questions / request additional fields, dynamically a checklist has to be associated to the request UseCase: When an alarm is received indicating an issue with a server’s performance, with the ...
          • 1. Query Basics

            Basic Query: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status" FROM WorkOrder wo LEFT JOIN SDUser sdu ON ...
          • Associate Checklist for a Request depending on Resource/UDF Values

            Requirement: Script to associate checklists depending on UDF Additional field values/Resource Question values. Usecase: In few organizations. they have a separate template for "New Asset Request".  Each new asset needs a different set of checklists ...