SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select manager > employees
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hayseed
Starting Member

6 Posts

Posted - 01/29/2009 :  17:32:34  Show Profile  Reply with Quote
I have been assigned one challenging query and am breaking it down into smaller tasks.

First task is: I need a select to show Managers, then any employees that report to them. Some of the employees are supervisors that have employees under them.

Here is my current query:

SELECT 
	p0.USER_NAME [Manager],
	p1.USER_NAME [UserLevel2],
	CASE WHEN p2.USER_NAME IS NULL THEN '' ELSE p2.USER_NAME END [UserLevel3],
	CASE 
		WHEN p2.USER_CONTRACTOR = 0 OR p1.USER_CONTRACTOR = 0 THEN 'E'
		WHEN p2.USER_CONTRACTOR = 1 OR p1.USER_CONTRACTOR = 1  THEN 'C'
	END [Employee/Contractor]
FROM T_USER p0
	LEFT JOIN (SELECT USER_NAME, USER_MANAGER_USER_FK, USER_PK, USER_CONTRACTOR FROM T_USER) p1 ON p1.USER_MANAGER_USER_FK = p0.USER_PK
	LEFT JOIN (SELECT USER_NAME, USER_MANAGER_USER_FK, USER_PK, USER_CONTRACTOR FROM T_USER) p2 ON p2.USER_MANAGER_USER_FK = p1.USER_PK
WHERE p0.USER_PK = 2005 
ORDER BY p1.USER_NAME, p2.USER_NAME


The data in the results is correct, but I am hoping for a different format.
Here is the current format:

Manager | UserLevel2 | UserLevel3 | Employee/Contractor
-------------------------------------------------------
Bill Smith | Jane Doe | Mark Jones | E
Bill Smith | Jane Doe | | E
Bill Smith | Mary Lambs | | C


The format I would like is:

Manager | UserLevel2 | UserLevel3 | Employee/Contractor
-------------------------------------------------------
Bill Smith | Jane Doe | Mark Jones | E
           | Jane Doe |            |E
           | Mark Lambs |          |C


Is this possible just using SQL?

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 01/29/2009 :  18:18:26  Show Profile  Reply with Quote
Highly doubtful that you can format the data that way. You need some kind of GROUP BY clause. I will look into it.
Go to Top of Page

Skorch
Constraint Violating Yak Guru

USA
300 Posts

Posted - 01/29/2009 :  18:50:53  Show Profile  Reply with Quote
So are you trying to output nulls/empty strings for the manager in your 2nd and 3rd rows? Sounds like a bad idea to me. What you're trying to do is done easily in SSRS or most other reporting platforms though.

Some days you're the dog, and some days you're the fire hydrant.
Go to Top of Page

Hayseed
Starting Member

6 Posts

Posted - 01/30/2009 :  09:28:55  Show Profile  Reply with Quote
I have to use the custom in-house report writer and there are limitations on how I can format.

I have done alot of research on Google and found nothing that would work. If I can not have the format I was hoping for, I can use the original format.

Just trying to score a few points with management
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/30/2009 :  09:40:08  Show Profile  Reply with Quote
quote:
Originally posted by Hayseed

I have to use the custom in-house report writer and there are limitations on how I can format.

I have done alot of research on Google and found nothing that would work. If I can not have the format I was hoping for, I can use the original format.

Just trying to score a few points with management


doesnt it have much formatting options? something like discard duplicates or remove duplicates for table cell? thats the property which gives you similar result in ssrs
Go to Top of Page

Hayseed
Starting Member

6 Posts

Posted - 01/30/2009 :  09:58:36  Show Profile  Reply with Quote
quote:
Originally posted by visakh16
doesnt it have much formatting options? something like discard duplicates or remove duplicates for table cell? thats the property which gives you similar result in ssrs



No, it does not have any format options other than a tabular report or a chart report. The output of the SQL results are pretty much the only format you have. That is why I was hoping for some kind of solution that could produce the desired results in SQL.

It's a limitation of the tools I have to use.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/30/2009 :  10:09:40  Show Profile  Reply with Quote
then use this rather ugly solution:-

SELECT CASE WHEN Seq=1  THEN [Manager] ELSE '' END,[UserLevel2],[UserLevel3],[Employee/Contractor]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY p0.USER_NAME,p1.USER_NAME ORDER BY p1.USER_NAME DESC) AS Seq,
	p0.USER_NAME [Manager],
	p1.USER_NAME [UserLevel2],
        p2.USER_NAME,
	CASE WHEN p2.USER_NAME IS NULL THEN '' ELSE p2.USER_NAME END [UserLevel3],
	CASE 
		WHEN p2.USER_CONTRACTOR = 0 OR p1.USER_CONTRACTOR = 0 THEN 'E'
		WHEN p2.USER_CONTRACTOR = 1 OR p1.USER_CONTRACTOR = 1  THEN 'C'
	END [Employee/Contractor]
FROM T_USER p0
	LEFT JOIN (SELECT USER_NAME, USER_MANAGER_USER_FK, USER_PK, USER_CONTRACTOR FROM T_USER) p1 ON p1.USER_MANAGER_USER_FK = p0.USER_PK
	LEFT JOIN (SELECT USER_NAME, USER_MANAGER_USER_FK, USER_PK, USER_CONTRACTOR FROM T_USER) p2 ON p2.USER_MANAGER_USER_FK = p1.USER_PK
WHERE p0.USER_PK = 2005 
)t
ORDER BY [UserLevel2], USER_NAME


Go to Top of Page

Hayseed
Starting Member

6 Posts

Posted - 01/30/2009 :  11:16:40  Show Profile  Reply with Quote
Thanks - that's much closer to what I need and should work just fine.

As I build more details (esp. the pivot) into the query and if I have other questions - should I start a new thread or
continue with this one
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/30/2009 :  11:57:27  Show Profile  Reply with Quote
if its related post here...else start new thread
Go to Top of Page

Hayseed
Starting Member

6 Posts

Posted - 01/30/2009 :  12:22:56  Show Profile  Reply with Quote
OK - thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/30/2009 :  12:26:21  Show Profile  Reply with Quote
welcome
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.86 seconds. Powered By: Snitz Forums 2000