SQL - Getting a Service Request priority in ServiceManagement

Zackary_Slates

Hello All,

When returning IR's and SRs from the WorkItem table I'd like to output Priority.  With SR's it gives me a GUID and with IR's it gives me a number.  SQL gets angry (types in red characters back at me!) when I join the Display table's ElementID field to the WorkItem's PriorityID field (Conversion failure...character string to uniqueidentifier).  I get why, but not sure the best way to work around this.

I'd ultimately like to have nice join solution, but realize this could be accomplished by a using Case and a subquery...

FYI I tried casting/converting to a uniqueidentifier hoping it would fill in the gaps for the IR's...no luck...must be the right length and correct format.

Any thoughts?

Shane_White

Hi @Zackary_Slates

How is this query:

SELECT WI.WorkItemId AS Id, WI.Title, WI.AffectedUser AS [Affected_User], WI.AssignedUser AS [Assigned_User], 

SGDisplay.DisplayString AS [Support_Group], StatusDisplay.DisplayString AS Status,

COALESCE(PriorityDisplay.DisplayString, WI.PriorityId) AS Priority, 

SourceDisplay.DisplayString AS Source, CONVERT(varchar, WI.Created, 103) + ' ' + CONVERT(varchar, WI.Created, 108) Created_Date

FROM WorkItem WI

LEFT JOIN DisplayString SGDisplay

ON WI.TierId = SGDisplay.ElementID

AND SGDisplay.LocaleID = 'ENU'

JOIN DisplayString StatusDisplay

ON WI.StatusId = StatusDisplay.ElementID

AND StatusDisplay.LocaleID = 'ENU'

JOIN DisplayString SourceDisplay

ON WI.SourceId = SourceDisplay.ElementID

AND SourceDisplay.LocaleID = 'ENU'

LEFT JOIN DisplayString PriorityDisplay

ON TRY_CONVERT(UNIQUEIDENTIFIER, WI.PriorityId) = PriorityDisplay.ElementID

AND PriorityDisplay.LocaleID = 'ENU'

EDIT - I have put in bold the specific bits that answer your question

Thanks,
Shane.

Zackary_Slates

Thanks!!! TRY_Convert...Yay new tool and it works perfectly.  Thanks!

Shane_White

Hi @Zackary_Slates

How is this query:

SELECT WI.WorkItemId AS Id, WI.Title, WI.AffectedUser AS [Affected_User], WI.AssignedUser AS [Assigned_User], 

SGDisplay.DisplayString AS [Support_Group], StatusDisplay.DisplayString AS Status,

COALESCE(PriorityDisplay.DisplayString, WI.PriorityId) AS Priority, 

SourceDisplay.DisplayString AS Source, CONVERT(varchar, WI.Created, 103) + ' ' + CONVERT(varchar, WI.Created, 108) Created_Date

FROM WorkItem WI

LEFT JOIN DisplayString SGDisplay

ON WI.TierId = SGDisplay.ElementID

AND SGDisplay.LocaleID = 'ENU'

JOIN DisplayString StatusDisplay

ON WI.StatusId = StatusDisplay.ElementID

AND StatusDisplay.LocaleID = 'ENU'

JOIN DisplayString SourceDisplay

ON WI.SourceId = SourceDisplay.ElementID

AND SourceDisplay.LocaleID = 'ENU'

LEFT JOIN DisplayString PriorityDisplay

ON TRY_CONVERT(UNIQUEIDENTIFIER, WI.PriorityId) = PriorityDisplay.ElementID

AND PriorityDisplay.LocaleID = 'ENU'

EDIT - I have put in bold the specific bits that answer your question

Thanks,
Shane.

Zackary_Slates

Thanks!!! TRY_Convert...Yay new tool and it works perfectly.  Thanks!