Good data, bad data, I'm the data with the gun.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Good data, bad data, I'm the data with the gun.

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-03-29 : 15:05:03

The subject has a thin thread to this post, and is a play on words from a line in a movie. Any guesses?

I've inherited 50,000 rows of users each has a column "EmployeeID" and "ReportsTo", each of type VARCHAR(20). (some of the data I've received is good, some is bad (e.g. blank EmployeeIDs. ergo, good data, bad data...)

ReportsTo is the EmployeeID of the user's manager.

CREATE TABLE MyTable (
Fullname VARCHAR(100),
EmployeeID VARCHAR(20),
ReportsTo VARCHAR(20)
)

Write a stored proc that will return all records of users at any of the first 5 levels. I don't care if we start with 0 or 1 as the top. Assuming 1 is the top, it should return the guy who reports to no one else (the CEO). Querying for the 2nd level of management, there should be about 10 reports which should be all the VPs... Querying for the 3rd level of management, should be the Directors, reporting to the VPs...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-29 : 15:54:06

Read through tree structure examples. Virtually the same thing.

try here: http://www.seventhnight.com/treestructs.asp

EDIT: a lot of the 'question answering queries' are in part #2

Corey