Sql Query to extract the data.. Need Help...

• Rajiv Kumar

  SSC Veteran

  Points: 227

  More actions

  August 14, 2010 at 6:13 am

  #227938

  I have table name employee with following fields -

  EmpID Name ManagerID

  against each employee, manager id is avaiable and a manager is also an employee...

  i need a sql query which will provide the list of all direct report with their status, that whether the direct report is a manager or individual contributor.

  for example - if empid 1 is a manager and under 1 there are 5 direct reports, out of 5, 4 direct reports are manager but 1 is individual contributor...

  i need the result set like - for EmpID 1 -

  there direct reports details -

  EmpID - Name - Manager_Status

  2 B Y

  3 C N

  4 D Y

  hope, i am able to explain my requirement, please do revert if you need any further details.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  August 14, 2010 at 1:32 pm

  #1207056

  Rajiv Kumar

  You are much more likely to receive a tested response if you would post the table definition, some sample data as per the first link in my signature block.

  Remember people here are not paid but willing volunteers how can not devote the time to writing create table scripts, guessing what your data is, etc., etc.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  August 15, 2010 at 4:39 pm

  #1207106

  There are a couple of options.

  CASE 1: Left outer join

  SELECT DISTINCT e.EmpID, e.Name, CASE WHEN sub.EmpID Is NULL THEN 'N' ELSE 'Y' END AS Manager_Status

  FROM Employee AS e

  LEFT OUTER JOIN Employee AS sub

  ON e.Employee_ID = sub.ManagerID

  You could also use a GROUP BY instead of DISTINCT.

  CASE 2: Exists with correlated subquery.

  SELECT e.EmpID, e.Name

  , CASE WHEN EXISTS (

  SELECT *

  FROM Employee AS sub

  WHERE sub.ManagerID = e.EmpID )

  THEN 'Y' ELSE 'N' END AS Manager_Status

  FROM Employee AS e

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Jeff Moden

  SSC Guru

  Points: 1004639

  More actions

  August 15, 2010 at 9:20 pm

  #1207135

  Rajiv Kumar (8/14/2010)

  ---

  I have table name employee with following fields -

  EmpID Name ManagerID

  against each employee, manager id is avaiable and a manager is also an employee...

  i need a sql query which will provide the list of all direct report with their status, that whether the direct report is a manager or individual contributor.

  for example - if empid 1 is a manager and under 1 there are 5 direct reports, out of 5, 4 direct reports are manager but 1 is individual contributor...

  i need the result set like - for EmpID 1 -

  there direct reports details -

  EmpID - Name - Manager_Status

  2 B Y

  3 C N

  4 D Y

  hope, i am able to explain my requirement, please do revert if you need any further details.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004639

  More actions

  August 15, 2010 at 9:22 pm

  #1207136

  Rajiv Kumar (8/14/2010)

  ---

  I have table name employee with following fields -

  EmpID Name ManagerID

  against each employee, manager id is avaiable and a manager is also an employee...

  i need a sql query which will provide the list of all direct report with their status, that whether the direct report is a manager or individual contributor.

  for example - if empid 1 is a manager and under 1 there are 5 direct reports, out of 5, 4 direct reports are manager but 1 is individual contributor...

  i need the result set like - for EmpID 1 -

  there direct reports details -

  EmpID - Name - Manager_Status

  2 B Y

  3 C N

  4 D Y

  hope, i am able to explain my requirement, please do revert if you need any further details.

  CREATE TABLE statements and readily consumable data would be a big help here. Please the the first link in BitBucket's signature line above for how to do that. These types of problems are actually pretty easy when you have a little data to play with.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)