Version : 14000

DB : MSSQL

OUTPUT:

Query  : Execute the below two queries 1 and 2 and get the roleids from the output and replace it in highlighted spot in query 3.

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

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

Query 3:

SELECT "ctdef"."NAME" AS "Change Type",
"chdt"."CHANGEID" AS "Change ID",
"chdt"."TITLE" AS "Title",
"chandes"."FULL_DESCRIPTION" AS "Description",
Longtodate("chanf"."UDF_DATE1") AS "Implementation Start Date/Time",
Longtodate("chanf"."UDF_DATE2") AS "Implementation End Date/Time",
"ownaaa"."FIRST_NAME" AS "Change Owner",
"catadef"."CATEGORYNAME" AS "Category",
"subcatadef"."NAME" AS "Subcategory",
"itemdef1"."NAME" AS "Item",
"stageDef"."DISPLAYNAME" AS "Stage",
"statusDef"."STATUSDISPLAYNAME" AS "Status",
"clcodeDef"."NAME" AS "Change Closure Code",
convert(varchar(10), ((chanf.UDF_DATE2)-(chanf.UDF_DATE1))/1000/3600/24)+':'+convert(varchar(10), (((chanf.UDF_DATE2)-(chanf.UDF_DATE1)) / (1000 * 60 * 60)) % 24)  "Time Spent Implementation difference (DD:HH)",
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=301 FOR XML PATH ('')), 1, 1, '') 'Project Manager',
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, '') 'Change Technician'  FROM "ChangeDetails" "chdt" LEFT JOIN "SDUser" "ownsd" ON "chdt"."TECHNICIANID"="ownsd"."USERID" LEFT JOIN "AaaUser" "ownaaa" ON "ownsd"."USERID"="ownaaa"."USER_ID" LEFT JOIN "ChangeTypeDefinition" "ctdef" ON "chdt"."CHANGETYPEID"="ctdef"."CHANGETYPEID" 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 "ChangeToDescription" "chandes" ON "chdt"."CHANGEID"="chandes"."CHANGEID" 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 "ChangeToClosureCode" "clcodeMapDef" ON "chdt"."CHANGEID"="clcodeMapDef"."CHANGEID" LEFT JOIN "Change_ClosureCode" "clcodeDef" ON "clcodeMapDef"."ID"="clcodeDef"."ID" WHERE  ( ( "chdt"."SITEID" IN (2382,2199,2198,2383,549,594,124,589,592,89,521,61,152,110,472,248,69,602,197,531,346,157,423,226,285,53,2400,2399,2406,2379,2191,2200,2380,2192,2428,2431,2414,2413,2415,183,416,286,149,540,541,362,177,268,160,211,525,332,148,318,361,433,274,137,283,431,223,32,125,552,456,236,455,2407,265,252,457,254,317,195,353,114,26,399,77,272,2409,2430,162,37,172,333,336,297,547,546,334,530,535,459,185,548,222,527,554,90,146,194,486,393,349,392,45,578,568,277,427,460,306,209,331,86,135,20,471,465,247,267,230,482,43,251,242,469,262,560,315,536,78,60,66,191,134,567,501,100,34,233,320,119,473,52,597,596,74,210,411,30,263,475,310,48,461,293,129,132,76,366,105,10,477,44,170,301,436,2293,2294,2197,485,198,335,2391,402,174,241,570,396,94,493,491,1865,2401,1864,1859,1861,2411,1863,1862,39,487,6,127,488,13,495,63,494,156,330,339,180,439,5,1208,23,2435,2436,1213,2434,1201,1202,1203,1211,1212,2405,1207,2378,1209,1210,1214,1215,513,2377,904,905,906,1205,907,902,903,901,1204,131,159,2195,2412,2196,529,337,354,539,216,64,352,217,109,454,188,553,341,1803,581,280,397,2402,2403,368,356,2418,583,2432,2420,2392,2398,2416,2404,2424,2425,2426,2427,168,2417,585,2419,143,435,311,550,523,136,2433,514,261,175,181,255,128,85,88,19,99,7,376,447,407,12,16,528,176,414,243,163,237,115,270,208,235,179,574,404,355,319,444,443,101,481,503,68,500,239,533,258,225,120,2390,196,121,169,365,284,276,428,193,46,47,2421,2423,2410,106,93,520,8,534,526,238,199,189,492,490,543,220,511,200,509,417,154,17,327,219,275,304,538,141,206,512,150,81,41,438,479,161,38,212,519,2397,2408,2429,36,91,2396,1501,2202,2194,2193,92,204) ) AND ( "chdt"."DELETEDTIME" IS NULL ) )