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

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

      • Query to show account additional fields _ Details required

        We Need to know whether that is a common account additional fields or add attribute under individual account.   So please send us the following screenshots.   1. Admin-> Account additional fields list view page.   2. Accounts->Edit account and show ...

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