Version : 10609

Database : MSSQL

OUTPUT:

Query to find Change Role ID :

      select chrol.name,chrolusermap.ROLEID from changeroles chrol LEFT JOIN  changeroleusermapping chrolusermap on chrolusermap.roleid = chrol.ID where chrol.name = 'Initimation'

Note : Kindly replace the name for which ever change role is needed.

OUTPUT :

SELECT "chdt"."CHANGEID" AS "Change ID",
    "catadef"."CATEGORYNAME" AS "Category",
    "ad"."ORG_NAME" AS "Account",
    "ctdef"."NAME" AS "Change Type",
    "orgaaa"."FIRST_NAME" AS "Change Requester",
    "chanf"."UDF_CHAR3" AS "Business Impact",
    "impactdef"."NAME" AS "Impact",
    "qd"."QUEUENAME" AS "Group",
    LONGTODATE("chdt"."CREATEDTIME") AS "Created Time",
    "subcatadef"."NAME" AS "Subcategory",
    LONGTODATE("chdt"."COMPLETEDTIME") AS "Completed Time",
    "tempDef"."NAME" AS "Change Template",
    "itemdef1"."NAME" AS "Item",
    "chdt"."TITLE" AS "Title",
    "chdt"."DESCRIPTION" AS "DESCRIPTION",
    "statusDef"."STATUSDISPLAYNAME" AS "Status",
    "stageDef"."DISPLAYNAME" AS "Stage",
    approvaldef.STATUSNAME "Approval Status",
    approLevel.NAME "Approval Level",
    approved_by_table.approved_user "Change Approved by" ,
    ApprovalDetails.email "Approver Email",
    STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum
    left join aaauser au  on au.user_id=crum.userid
    where chdt.changeid=crum.changeid and crum.roleid=601 FOR XML PATH ('')), 1, 1, '') 'Initimation',
    ci.ciname AS "Asset Involved" FROM "ChangeDetails" "chdt"
    LEFT JOIN "SDUser" "orgsd" ON "chdt"."INITIATORID"="orgsd"."USERID"
    LEFT JOIN "AaaUser" "orgaaa" ON "orgsd"."USERID"="orgaaa"."USER_ID"
    LEFT JOIN "ChangeTypeDefinition" "ctdef" ON "chdt"."CHANGETYPEID"="ctdef"."CHANGETYPEID"
    LEFT JOIN "ApprovalStatusDefinition" "approvaldef" ON "chdt"."APPR_STATUSID"="approvaldef"."STATUSID"
    LEFT JOIN "CategoryDefinition" "catadef" ON "chdt"."CATEGORYID"="catadef"."CATEGORYID"
    LEFT JOIN "SubCategoryDefinition" "subcatadef" ON "chdt"."SUBCATEGORYID"="subcatadef"."SUBCATEGORYID"
    LEFT JOIN "ItemDefinition" "itemdef1" ON "chdt"."ITEMID"="itemdef1"."ITEMID"
    LEFT JOIN "ImpactDefinition" "impactdef" ON "chdt"."IMPACTID"="impactdef"."IMPACTID"
    LEFT JOIN "QueueDefinition" "qd" ON "chdt"."GROUPID"="qd"."QUEUEID"
    LEFT JOIN "ChangeToCharge" "changeCharge" ON "chdt"."CHANGEID"="changeCharge"."CHANGEID"
    LEFT JOIN "ChargesTable" "chargeTable" ON "changeCharge"."CHARGEID"="chargeTable"."CHARGEID"
    LEFT JOIN "AaaUser" "ownaaa1" ON "chargeTable"."TECHNICIANID"="ownaaa1"."USER_ID"
    LEFT JOIN "Change_Fields" "chanf" ON "chdt"."CHANGEID"="chanf"."CHANGEID"
    LEFT JOIN "Change_StageDefinition" "stageDef" ON "chdt"."WFSTAGEID"="stageDef"."WFSTAGEID"
    LEFT JOIN "Change_StatusDefinition" "statusDef" ON "chdt"."WFSTATUSID"="statusDef"."WFSTATUSID"
    LEFT JOIN "ChangeTemplate" "tempDef" ON "chdt"."TEMPLATEID"="tempDef"."TEMPLATEID"
    INNER JOIN "AccountSiteMapping" "asm" ON "chdt"."SITEID"="asm"."SITEID"
    INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID"
    LEFT JOIN ApprStageToChange ON chdt.CHANGEID=ApprStageToChange.CHANGEID
    LEFT JOIN "Changetoci" "chanci" ON "chdt"."CHANGEID"="chanci"."CHANGEID"
    LEFT JOIN ci ON "chanci"."CIID"=ci."CIID"
    LEFT JOIN ApprovalDetails ON ApprStageToChange.APPROVAL_STAGEID=ApprovalDetails.APPROVAL_STAGEID
    LEFT JOIN ApprovalHistory aph ON ApprovalDetails.APPROVALID=aph.APPROVALID
    Left join sduser sd on approvaldetails.approverid=sd.userid
    left join aaauser aaa on sd.userid=aaa.user_id
    LEFT JOIN ApprovalStage ON ApprovalDetails.APPROVAL_STAGEID=ApprovalStage.APPROVAL_STAGEID
    LEFT JOIN Approvallevel approLevel on ApprovalDetails.approval_level_id=approLevel.id
    LEFT JOIN (SELECT comments.changeid, commenteduser.first_name AS approved_user FROM changestatuscomments comments LEFT JOIN sduser "sdu1" ON  comments.commentedby=sdu1.userid LEFT JOIN aaauser "commenteduser" ON sdu1.userid= commenteduser.user_id LEFT JOIN change_statusdefinition cstd ON comments.wfstatusid=cstd.wfstatusid LEFT JOIN change_stagedefinition csgd ON comments.wfstageid=csgd.wfstageid WHERE statusname='Approved' and csgd.name='Approval')approved_by_table ON chdt.changeid=approved_by_table.changeid

Note : Kindly replace the roleid in second query which is highlighted in red font with the value which can retrieved from first query.