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 ADSelfService Plus?
Related Articles
VPAT Document for ServiceDesk Plus On-premises
A Voluntary Product Accessibility Template (VPAT) is a document that explains how a product's accessibility meet the revised section 508 and EN 301549 and EN 301549 standards based on the Web Content Accessibility Guidelines (WCAG) requirements. ...