Seperating data in SQL

We are building a report out of SQL for a phone list for our agency. Our SQL server (2008) is linked to our AD server and I am pulling over user information into a view which I then pull into VS to report on. The information in AD for each user contains all of the information for the user such as work address, phone numbers and department. Most users work in only 1 department, but a few work in multiple such as Department 1 & Department 2 OR Dept 1, Dept 2 & Dept 3.

On our report I have each department separated out and each employee listed under the department. The users with multiple departments though do not show up because their department does not match the header, ie; Department 1 doesn't equal Dept 1, Dept 2 & Dept 3.

Management would like me to list the users who work in multiple departments under each department header on the report. Does anyone have some thoughts on how I might accomplish this with the way things are setup? I can post the queries if needed.
LVL 1
GileadITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
You could create a query for each department and UNION them together, but you would need to update the query every time a new department is added. You may need to post your query for better advice:

SELECT * FROM Blah WHERE [DEPT] = Department1
UNION ALL
SELECT * FROM Blah WHERE [DEPT] = Department2
UNION ALL
SELECT * FROM Blah WHERE [DEPT] = Department3
0
GileadITAuthor Commented:
Hi Russel,
Below is the query I use to create the view in SQL from AD. The query was much simpler before I plopped it into SQL, as SQL added a few things to it which make it look more complicated than it really is. The end result though is it grabs everyone I need

SELECT     TOP (100) PERCENT userAccountControl, mail, facsimileTelephoneNumber, postalCode, st, l, streetAddress, mobile, telephoneNumber, title, department,
                      displayName
FROM         OPENQUERY(ADSI,
                      'SELECT displayName, department, title, telephoneNumber, mobile, streetAddress, l, st, postalCode, facsimileTelephoneNumber, mail, userAccountControl FROM ''LDAP://DC=domain,DC=lan'' WHERE objectClass = ''Person'' AND OBJECTClass <> ''Computer''')
                       AS derivedtbl_1
WHERE     (userAccountControl <> 2) AND (userAccountControl <> 66080) AND (userAccountControl <> 514) AND (telephoneNumber IS NOT NULL) AND (mail IS NOT NULL)
ORDER BY department, displayName

There is a table in SQL as well that I created that has each department name, it's address, telephone, etc... that gets thrown into a dataset which I explain below.

In VS, the dataset used for the report joins the table and view in SQL on the program name of each. This disregards the users who have multiple programs. I would like to find a way to seperate out the folks with multiple programs and instead of splitting the value into separate columns, separate them into separate rows.
0
Russell FoxDatabase DeveloperCommented:
So do users have multiple records in the query above? Like

displayname    department
Russell            Tech
Russell            Management


And in your department table there would be separate entries for Tech and Management that you join on the [department] field in your query above?

department      address         telephone
Tech                          1st Street      555-555-5551
Management            19th Street   555-555-5552


And what you want them combined like this (but with more fields, of course):

displayname  department  departmentAddress    departmentTelephone
Russell            Tech                      1st Street                      555-555-5551
Russell            Management        19th Street                   555-555-5552


Can you create a single query like this:
SELECT TOP (100) PERCENT 
	t1.userAccountControl, 
	t1.mail, 
	t1.facsimileTelephoneNumber, 
	t1.postalCode, 
	t1.st, 
	t1.l, 
	t1.streetAddress, 
	t1.mobile, 
	t1.telephoneNumber, 
	t1.title, 
	t1.displayName,
	t2.* -- get all department info, too
FROM OPENQUERY(
	ADSI, 
	'SELECT displayName, department, title, telephoneNumber, mobile, streetAddress, l, st, postalCode, facsimileTelephoneNumber, mail, userAccountControl FROM ''LDAP://DC=domain,DC=lan'' WHERE objectClass = ''Person'' AND OBJECTClass <> ''Computer'''
	) AS t1
	JOIN MyDepartments t2
		ON t1.department = t2.department
WHERE (t1.userAccountControl <> 2) AND (t1.userAccountControl <> 66080) AND (t1.userAccountControl <> 514) AND (t1.telephoneNumber IS NOT NULL) AND (t1.mail IS NOT NULL)
ORDER BY t1.department, t1.displayName

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

GileadITAuthor Commented:
So do users have multiple records in the query above? Like

displayname    department
Russell            Tech
Russell            Management

No. Each user is listed only once from the query. If the user is in multiple departments, it will just list them once and in the department field both departments would be listed.

And in your department table there would be separate entries for Tech and Management that you join on the [department] field in your query above?

department      address         telephone
Tech                          1st Street      555-555-5551
Management            19th Street   555-555-5552

Correct.

And what you want them combined like this (but with more fields, of course):

displayname  department  departmentAddress    departmentTelephone
Russell            Tech                      1st Street                      555-555-5551
Russell            Management        19th Street                   555-555-5552

Correct.

As for the sql statement you provided, I can't tell what is different from it than the one I provided. I will run it though and tell you what happens.

Thanks for all your help so far Russell. I have been banging my head on this for the better part of the day. It's great to actually work with someone on this!
0
GileadITAuthor Commented:
I just ran it but it didn't show any of the users with multiple departments in the department field.
0
Russell FoxDatabase DeveloperCommented:
Yeah, that's tricky. How are they stored in the AD department field? Like "Tech, Management"? The join on [department] will not work because "Tech" <> "Tech, Management". Try this:

      JOIN MyDepartments t2
            ON t2.department LIKE '%' + t1.department + '%'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GileadITAuthor Commented:
OK, I definitely got much closer with the above code. Everyone with multiple departments is now showing up multiple times for each iteration of the actual department name. I think at this point I can play with it a bit more and get it to be what I need. I have to say, I never thought to use a LIKE statement on a join. Thanks for your out of the box thinking!
0
Russell FoxDatabase DeveloperCommented:
It's probably horribly inefficient, but as long as you aren't hitting massive data sets with it, it should be fine. Let me know if you need any more help!
0
GileadITAuthor Commented:
Thanks Russell for all your help. The code you helped me with is now working to populate the view in SQL which I then bring into a dataset within VS. Today I have been working on the report and everyone is listed on it as they should be. Again, thanks for helping me stop banging my head against the wall!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.