Query to show solution details with approver and associated requests

Query to show solution details with approver and associated requests

MSSQL:

SELECT solution.solutionid "Solution ID" ,

Ad.org_name "Account",

max(Solution.TITLE) "Solution Title",

max(au.first_name) "Solution Approver",

max(KB_Topics.TOPICNAME) "Solution Topic",

max(Solution_Keywords.keyword) "Keywords",

max(owner.FIRST_NAME) "Solution Owner",

LONGTODATE(max(solutioninfo.review_date)) "Review Date",

LONGTODATE(max(solutioninfo.expiry_date)) "Expiry Date",

max(sch.comments) "Comments",

max(case when solutioninfo.ispublic='1' then 'Public' else 'Private' end) "Publish to Self Service Portal",

max(Creator.FIRST_NAME) "Created By",

Max(Updater.FIRST_NAME) "Last Updated By",

max(Sol_StatusDefinition.STATUSNAME) "Status",

max(solutioninfo.noofhits) "Views",

longtodate(max(SolutionInfo.CREATEDTIME)) "Created On",

longtodate(max(SolutionInfo.LASTUPDATEDTIME)) "Last Updated On",

Max(sch.operation) "History (Last Operation)",

LONGTODATE(max(sch.updatedtime)) "History (Updated Time)",

max(aau.first_name) "History (Updated By)",

STUFF((SELECT ','+ cast(str.requestid as varchar) + char(10)   FROM  solutiontorequest str

where solution.solutionid=str.solutionid FOR XML PATH ('')), 1, 1, '') "Requests" FROM Solution

INNER JOIN SolutionInfo ON Solution.SOLUTIONID=SolutionInfo.SOLUTIONID

LEFT JOIN KB_Topics ON Solution.TOPICID=KB_Topics.TOPICID

LEFT JOIN AaaUser Updater ON SolutionInfo.LASTUPDATEDBY=Updater.USER_ID

LEFT JOIN AaaUser Creator ON SolutionInfo.CREATEDBY=Creator.USER_ID

LEFT JOIN Solution_Keywords ON Solution.SOLUTIONID=Solution_Keywords.SOLUTIONID

LEFT JOIN Sol_StatusDefinition ON Solution.STATUSID=Sol_StatusDefinition.STATUSID

LEFT JOIN Sol_commenthistory sch ON Solution.solutionid=Sch.solutionid

LEFT JOIN aaauser aau On sch.updatedby=aau.user_id

LEFT JOIN sduser on solutioninfo.createdby=sduser.userid

LEFT JOIN solutionownerdefinition solown ON solown.solutionid=solution.solutionid

LEFT JOIN aaauser owner on owner.user_id=solown.ownerid
LEFT JOIN solutionugmapping sum ON solutioninfo.solutionid=sum.solutionid
LEFT JOIN Usergroups ug ON sum.usergroupid=ug.id
LEFT JOIN Usergroupsaccmapping ugam ON ug.id=ugam.usergroupid
LEFT JOIN AccountDefinition ad ON ugam.accountid=ad.org_id
LEFT JOIN Solution_Approvers sa ON solution.solutionid=sa.solutionid
LEFT JOIN Aaauser au ON sch.UPDATEDBY=au.user_id
GROUP BY solution.solutionid, au.FIRST_NAME, Ugam.ACCOUNTID,  ad.ORG_NAME ORDER BY 2

          • Related Articles

          • 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",  ...
          • Query to show Approved tickets per Approver

            PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL "Approver Email", asd.stagename "Stage", ...
          • 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", ...
          • Query to show Problem details, timespent and its associated request IDs

            ​MSSQL: SELECT "prob"."PROBLEMID" AS "Problem ID",  "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status",  "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...
          • Query to show resolved by value

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.assignedtime) "Assigned Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", aau.FIRST_NAME AS "Requester", qd.QUEUENAME AS "Group", ...