Query to show Solutions and its associated fields

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", 
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, '') "Requestid" 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
GROUP BY solution.solutionid

          • 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 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", ...

          • Enable Account Details for basic technicians

            Account Details is an icon that is found right next to the Account drop-down in the application.  For 9.4 Builds By default Account Details tab is shown ONLY for SDAccountAdmin and SDAdmin roles.   1. Take a trimmed backup/MSSQL Backup/Server ...

          • Query to show Contract details with assets associated

            PGSQL: SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ad.ORG_NAME AS "Account",  ...

          • Query to show user additional fields along with requests

            MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", technician.attribute_302 "Technician Business Unit", aau.FIRST_NAME AS "Requester", ...