Query to show Problems, its associated incidents and change_ MSSQL

Query to show Problems, its associated incidents and change_ MSSQL

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", "impactdef"."NAME" AS "Problem Impact", "probdesc"."FULL_DESCRIPTION" AS "Problem Description", LONGTODATE("woproblem"."REPORTEDTIME") AS "Problem Reported Date", LONGTODATE("woproblem"."UPDATEDTIME") AS "Problem Updated Date", LONGTODATE("woproblem"."DUEBYTIME") AS "Problem DueBy Date", LONGTODATE("woproblem"."CLOSEDTIME") AS "Problem Closed Date","solwork"."DESCRIPTION" AS "Workaround","solres"."DESCRIPTION" AS "Solution", wo.WORKORDERID AS "Associated Request IDs", wo.TITLE AS "Request Subject", cd.CATEGORYNAME AS "Request Category", scd.NAME AS "Request Subcategory",icd.NAME AS "Request Item",  ti.FIRST_NAME AS "Request Technician",  "sdo"."NAME" AS "Site","sdu"."ISVIPUSER" AS "VIP User","ad"."ORG_NAME" AS "Account", chd.changeid "Associated Change ID", chd.title "Change Subject" FROM WorkOrder wo LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID 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 WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID LEFT JOIN ProblemToIncidentMapping wotoproblemdet ON wo.WORKORDERID=wotoproblemdet.WORKORDERID LEFT JOIN Problem woproblem ON wotoproblemdet.PROBLEMID=woproblem.PROBLEMID LEFT JOIN Problemtochangemapping ptc ON woproblem.PROBLEMID=ptc.problemid LEFT JOIN Changedetails chd ON ptc.changeid=chd.changeid LEFT JOIN "PriorityDefinition" "priodef" ON "woproblem"."PRIORITYID"="priodef"."PRIORITYID" LEFT JOIN "UrgencyDefinition" "urgdef" ON "woproblem"."URGENCYID"="urgdef"."URGENCYID" LEFT JOIN "CategoryDefinition" "catadef" ON "woproblem"."CATEGORYID"="catadef"."CATEGORYID" LEFT JOIN "ProblemResolution" "probResol" ON "woproblem"."PROBLEMID"="probResol"."PROBLEMID" LEFT JOIN "StatusDefinition" "statdef" ON "woproblem"."STATUSID"="statdef"."STATUSID" LEFT JOIN SDUSER sdu ON woproblem.originatorid=sdu.userid
 LEFT JOIN "ImpactDefinition" "impactdef" ON "probResol"."IMPACTID"="impactdef"."IMPACTID"
 LEFT JOIN "ProblemToDescription" "probdesc" ON "woproblem"."PROBLEMID"="probdesc"."PROBLEMID"
 LEFT JOIN "SolutionToWorkAround" "probwork" ON "woproblem"."PROBLEMID"="probwork"."PROBLEMID"
 LEFT JOIN "Solution" "solwork" ON "probwork"."SOLUTIONID"="solwork"."SOLUTIONID"
 LEFT JOIN "SolutionToResolution" "probres" ON "woproblem"."PROBLEMID"="probres"."PROBLEMID"
 LEFT JOIN "Solution" "solres" ON "probres"."SOLUTIONID"="solres"."SOLUTIONID"
 LEFT JOIN "SiteDefinition" "siteDef" ON "woproblem"."SITEID"="siteDef"."SITEID"
 LEFT JOIN "SDOrganization" "sdo" ON "siteDef"."SITEID"="sdo"."ORG_ID"
 INNER JOIN "AccountSiteMapping" "asm" ON "woproblem"."SITEID"="asm"."SITEID"
 INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" WHERE (wo.ISPARENT='1') AND woproblem.PROBLEMID=1 ORDER BY 1
          • Related Articles

          • Query to show technicians associated accounts, sites and Support groups - MSSQL

            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 Title", sdo.name "Associated ...
          • Query to show technicians and associated groups_PGSQL

            SELECT AaaUser.FIRST_NAME "Technician Name", (sdo.NAME) "Associated Site", array_to_string(array_agg(distinct(qd.queuename)), ',') "Associated Group" FROM AaaUser  left JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID  inner JOIN HelpDeskCrew ON ...
          • Query to show Change Roles in change requests

            MSSQL: SELECT "chdt"."CHANGEID" AS "Change ID", "chdt"."TITLE" AS "Title", LONGTODATE("chdt"."CREATEDTIME") AS "Created Time", LONGTODATE("chdt"."SCHEDULEDSTARTTIME") AS "Scheduled Start Time", LONGTODATE("chdt"."SCHEDULEDENDTIME") AS "Scheduled ...
          • Query to show worklogs under change tasks

            PGSQL: SELECT ad.ORG_NAME AS "Account", tk.taskid "Task ID", COALESCE(ctk.CHANGEID , ch.CHANGEID) "Change ID", to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Change' "Module",cast((sum(ct.TIMESPENT)/1000 * interval '1 ...
          • Query to show Solutions and its associated fields

            MSSQL: SELECT solution.solutionid "Solution ID" , max(Solution.TITLE) "Solution Title",  max(KB_Topics.TOPICNAME) "Solution Topic", max(Solution_Keywords.keyword) "Keywords",  max(owner.FIRST_NAME) "Solution Owner",  ...