Lookup two Display Names using the same Table on 1 unique assignment in SQL Tables

Question

I am trying to write a query where two different UIDs need to lookup a Resource Name for both, but separately.

In other words, for each Task, there are resources assigned and one status manager. This converts in SQl to an Assignment, unique to a resource, but with the same status manager. However, no where in the database can one see the Status Manager's Name on a given assignment.

The assignment does have "TaskStatusManagerUID" available. The name of the Status Manager can be determined by tying it back to MSP_EPMResource table where TaskStatusManagerUID = ResourceUID.

The catch is, for my report, I need to be able to look at the ResourceUID and TaskstatusManagerUID and determine the names of each on the same assignment.

While I have been successful with a join to display the name for one or the other, I have not been able to determine how to show the name for both the Resource and TaskStatusManager.

This is an example of what I am trying to display (parentheses added for readability):

(AssignmentUID) (Task Name) (Resource Name) (Task Status Manager Name)

See more info below:

This is the code I have been working with, but have been unsuccessful:

Select top 100
c.[assignmentuid], 
a.[taskname], 
c.[resourceuid],
b.[resourcename], 
a.[taskstatusmanageruid],
d.[StatusManager]

from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmAssignment] c
join [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmTask_UserView] a
on a.[TaskUID] = c.[TaskUID]
join [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b
on b.[ResourceUID] = c.[ResourceUID]

join (select b.resourcename StatusManager
from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b) d 
on d.[StatusManager] = a.[taskstatusmanageruid] 

group by 
c.[assignmentuid], 
a.[taskname], 
c.[resourceuid],
b.[resourcename], 
a.[taskstatusmanageruid],
d.[StatusManager]

Currently, I am getting "Conversion failed when converting from a character string to uniqueidentifier."

Answer 1

On your joins you have on a.[TaskUID] = c.[TaskUID], on b.[ResourceUID] = c.[ResourceUID], and on d.[StatusManager] = a.[taskstatusmanageruid], of which, I am assuming that the last one is causing you the issue. Try instead

join (select b.resourcename StatusManager
from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b) d 
on d.[StatusManager] = CONVERT(CHAR, a.[taskstatusmanageruid])

This will convert the GUID contained in taskstatusmanageruid to a char string, allowing it to compare successfully.

You could also, instead of converting the value, cast the value CAST(a.[taskstatusmanageruid] AS CHAR

EDIT

Due to the nature of the GUID, you may not be able to convert/cast it to a char value, in which case you would need to convert/cast both fields to either varchar or nvarchar:

join (select b.resourcename StatusManager
from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b) d 
on CONVERT([N]VARCHAR, d.[StatusManager]) = CONVERT([N]VARCHAR, a.[taskstatusmanageruid])

OR

join (select b.resourcename StatusManager
from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b) d 
on CAST(d.[StatusManager] AS [N]VARCHAR) = CAST( a.[taskstatusmanageruid] AS [N]VARCHAR)