I would like to create a view that shows each employee's id and there vp's information, if the vp's id is a part of the dbo.HIERARCHY.Hierarchy field (which is a string containing the employee's entire hierarchy).
In enterprise manager it accepts this as a parsable query, but it isn't getting me the data I need. I suspect it has something to do with me not knowing how to add the wildcard characters when searching the dbo.HIERARCHY.Hierarchy field. Any idea how I can fix this?
SELECT dbo.CCINFORMATION.*, dbo.HIERARCHY.AWID AS Employee
FROM dbo.CCINFORMATION INNER JOIN
dbo.HIERARCHY ON dbo.CCINFORMATION.AWID LIKE dbo.HIERARCHY.Hierarchy
WHERE (dbo.CCINFORMATION.Title LIKE '%VP,%') OR
(dbo.CCINFORMATION.Title LIKE '%CEO%') OR
(dbo.CCINFORMATION.Title LIKE '%VICE PR%')
I'm no expert, but I think that for a one time search this query would work fine. As part of a process however, it would be much more efficient to definitavely identify employess as a 'CEO' or 'VP' in a seperate column.
From the query it appears the Title values are something like 'VP of Marketing', 'VP of Finance', etc. For frequent searches on specific portions of this column, it would meake sense to me to standardize the portion you are searching on in its own column.
Hard-coding strings like 'VP', 'CEO', 'VICE PR' just invites problems when someone enters a title like 'V.P.', 'C.E.O.', or 'Big Kahuna', or when some mid-level manager or department head requests to be included.