Hey Experts,
Here is the query I currently have:
SELECT
CodestoCollectors.id AS CodeID,
CodestoCollectors.Collecto
rCode,
Collectors.CollectorLast + ', ' + Collectors.CollectorFirst AS Name,
CodestoCollectors.Manager,
Collectors.CollectorNumber
,
Collectors.Branch,
CodestoCollectors.Client AS ClientCode,
Collectors.Position AS PositionCode,
Branches.BranchName,
COA_Positions.Position,
COA_Clients.Client
FROM
COA_Clients
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.Collecto
rNumber = Collectors.CollectorNumber
WHERE
(Collectors.Employed = 'Y') AND
(UPPER(Collectors.Collecto
rLast) <> 'ZZHOUSE')
ORDER BY
Collectors.CollectorLast,
Collectors.CollectorFirst,
CodestoCollectors.Collecto
rCode
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.Collecto
rNumber 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.
Thanks
Start Free Trial
