Here is the query I currently have:
CodestoCollectors.id AS CodeID,
Collectors.CollectorLast + ', ' + Collectors.CollectorFirst AS Name,
CodestoCollectors.Client AS ClientCode,
Collectors.Position AS PositionCode,
RIGHT OUTER JOIN CodestoCollectors ON COA_Clients.code = CodestoCollectors.Client
FULL OUTER JOIN Collectors
LEFT OUTER JOIN COA_Positions ON Collectors.Position = COA_Positions.code
LEFT OUTER JOIN Branches ON Collectors.Branch = Branches.Branch
ON CodestoCollectors.CollectorNumber = Collectors.CollectorNumber
(Collectors.Employed = 'Y') AND
(UPPER(Collectors.CollectorLast) <> 'ZZHOUSE')
My problem is that I need to add another field, Manager Name, which would be SELECT C.CollectorLast + ', ' + C.CollectorFirst as ManagerName from Collectors as C INNER JOIN ....
The problem I am having is that I need to combine this query as part of the above query, and the join is on CodesToCollectors.Manager=C.CollectorNumber UNLESS CodesToCollectors.Manager IS NULL, in which case I will join on Collectors.Manager.
I have no idea how to form the CASE WHEN statement, and where to place it (or even if I should use it) in order to get the same results but with the manager name coming back rather than the number (as it is now).
Please help with this.