question

natongm avatar image
natongm asked

List all consultants and their managers

I am able to extract all the information required but not getting managers in the resultset. My resultset lists all consultants, but not who their managers are SELECT Consultant.ConsultantID , Consultant.FirstName , Consultant.LastName , cast(StartDate as date) [Start Date] , Consultant.CareerLevel, Sales.RetailSales , Sales.RetailSalesTotal , Sales.Unit , Unit.UnitName ,Consultant.AccountStatus , CareerLevel.CareerLevelName --lists if consultant or manager FROM Consultant INNER JOIN Sales ON Consultant.ConsultantID = Sales.ConsultantID INNER JOIN Unit ON Consultant.UnitID = Unit.UnitID INNER JOIN CareerLevel ON CareerLevel.CareerLevelID = Consultant.CareerLevelID WHERE Startdate between '01/01/2010' and '12/31/2012'
t-sql
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
How would you know who is the manager of a consultant. Your query contains no tracks of a ManagerID (or similar) column, nor did you post any DDL for your tables, so it's hard to tell. On a more conceptual note: Look at recursive CTEs.
2 Likes 2 ·
SirSQL avatar image SirSQL commented ·
Table schemas would be helpful here. For example why would a manager be listed in a consultant table? It doesn't make a lot of sense.
1 Like 1 ·
natongm avatar image natongm commented ·
CareerLevelID determines if a consultant is a director or not. So, I am looking to display the names of directors as well as the consultants below them. CareerLevel 6+ is director
0 Likes 0 ·
natongm avatar image natongm commented ·
CareerLevelID determines if director or not. CareerLevelID of 6 and up is director level. CareerLevelID is a flag in Consultant table.
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Ok. So now we know who is à director. But My question was about the relation a consultant has to à director. How do one know which director is Consultant A:s manager?
0 Likes 0 ·

1 Answer

·
natongm avatar image
natongm answered
Below is a rudimentary table schemas of CareerLevel and Consultant. Any consultant who has a careerlevel of 6 or more is a director and has other consultants under him/her *CareerLevel Table* CareerLevelID int CareerLevel int CareerLevelName varchar *Consultant Table* ConsultantID int Consultant varchar CareerLevel int (if consultant careerlevel >= 6, then consultant is director) IsDirector int Firstname LastName
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

natongm avatar image natongm commented ·
Below is a rudimentary table schemas of CareerLevel and Consultant. Any consultant who has a careerlevel of 6 or more is a director and has other consultants under him/her CareerLevel Table CareerLevelID int CareerLevel int CareerLevelName varchar Consultant Table ConsultantID int Consultant varchar CareerLevel int (if consultant careerlevel >= 6, then consultant is director) IsDirector int Firstname LastName
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.