?
Solved

Seperating data in SQL

Posted on 2014-08-21
9
Medium Priority
?
226 Views
Last Modified: 2014-08-22
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.
0
Comment
Question by:GileadIT
  • 5
  • 4
9 Comments
 
LVL 14

Expert Comment

by:Russell Fox
ID: 40277138
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
 
LVL 1

Author Comment

by:GileadIT
ID: 40277247
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
 
LVL 14

Expert Comment

by:Russell Fox
ID: 40277347
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 1

Author Comment

by:GileadIT
ID: 40277424
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
 
LVL 1

Author Comment

by:GileadIT
ID: 40277446
I just ran it but it didn't show any of the users with multiple departments in the department field.
0
 
LVL 14

Accepted Solution

by:
Russell Fox earned 2000 total points
ID: 40277808
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
 
LVL 1

Author Comment

by:GileadIT
ID: 40277843
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
 
LVL 14

Expert Comment

by:Russell Fox
ID: 40279175
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
 
LVL 1

Author Closing Comment

by:GileadIT
ID: 40279465
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question