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.
Author |
Topic |
randolphoralph
Starting Member
7 Posts |
Posted - 2012-11-27 : 18:52:45
|
I am trying to create a query that will return information where field values are the same.Here is an example of the tableOffice OfficeType Employee EmployeeTypeEast Division East Division Bill Smith Full TimeEast Division Sally Smith Part TimeEast Division Bill Smith Maternity LeaveWest FieldWest Field John Smith Part TimeSouth Division South Division Sue Smith Part TimeSouth Division John Smith Part TimeSouth Division Jimmy Smith Part TimeNorth DivisionI am looking for the query to return the Offices where the Office Type is equal to Division and No Employee or if the Office Type is equal to Division and EmployeeType is the same for each employee under that office. There are over 150 different EmployeeType values so I wanted to do this without having to specify each different value in the query.Based on the sample table above here is what I would like the query to return.Office OfficeType EmployeeTypeSouth Division Part TimeNorth DivisionIs this even possible? |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-11-27 : 20:30:56
|
I gotta believe that there is an simpler way of doing this but...[CODE]declare @offices table ( Office varchar(10), OfficeType varchar(10), Employee varchar(50), EmployeeType varchar(20) )insert into @officesvalues ('East', 'Division', null, null), ('East', 'Division', 'Bill Smith', 'Full Time'), ('East', 'Division', 'Sally Smith', 'Part Time'), ('East', 'Division', 'Bill Smith', 'Maternity Leave'), ('West', 'Field', null, null), ('West', 'Field', 'John Smith', 'Part Time'), ('South', 'Division', null, null), ('South', 'Division', 'Sue Smith', 'Part Time'), ('South', 'Division', 'John Smith', 'Part Time'), ('South', 'Division', 'Jimmy Smith', 'Part Time'), ('North', 'Division', null, null);with Divisionsas (select Office, OfficeType, EmployeeType, count(EmployeeType) over (partition by Office) CountTypesfrom @officeswhere OfficeType = 'Division'),DivSingleType as (select Office, count (distinct EmployeeType) CountTypesfrom divisionsgroup by Officehaving count (distinct EmployeeType) = 1)select Office, OfficeType, EmployeeTypefrom Divisionswhere CountTypes = 0union allselect distinct a.Office, a.OfficeType, a.EmployeeTypefrom Divisions ainner join DivSingleType b on a.Office = b.Office and a.EmployeeType is not null[/CODE]=================================================Hear the sledges with the bells - silver bells! What a world of merriment their melody foretells! |
|
|
randolphoralph
Starting Member
7 Posts |
Posted - 2012-11-27 : 21:22:20
|
Thank you Bustaz Kool. I am not sure your code will work for me for two reasons.First off the database admin has only been given me select permissions to the database.The second issue is that I have thousands of rows of data, and if I understand your code correctly I would have to insert the values in the code which would insert them into a table. |
|
|
randolphoralph
Starting Member
7 Posts |
Posted - 2012-11-27 : 21:30:12
|
After further review of the code may I was wrong. Does the code create a temporary table? Can temporary tables be created with select permissions?There has to be a solution that will not require the values to be typed into the code.I appreciate all the help! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-27 : 21:57:49
|
You can use temp table.Select Office,OfficeType,nullfrom(Select Office,OfficeType,Count(*) CNTfrom @officesWhere OfficeType = 'Division'Group by Office,OfficeTypeHaving Count(*) = 1)PUnionSelect A.Office,A.OfficeType,A.EmployeeTypefrom(Select Office,OfficeType,EmployeeType,Count(*) CNTAfrom @officesWhere OfficeType = 'Division'Group by Office,OfficeType,EmployeeTypeHaving Count(*) > 1)Ainner join(Select Office,OfficeType,Count(*) CNTBfrom @officesWhere OfficeType = 'Division'Group by Office,OfficeTypeHaving Count(*) > 1)B on A.Office = B.Office and A.OfficeType = B.OfficeType and A.CNTA = B.CNTB |
|
|
randolphoralph
Starting Member
7 Posts |
Posted - 2012-11-28 : 10:42:17
|
Well that code would have worked perfectly, but I realized the sample table I provided was incorrect.It should be as followsOffice Type EmployeeEast Division East Full Time Bill SmithEast Part Time Sally SmithEast Maternity Leave Sarah SmithWest FieldWest Part Time John SmithSouth Division South Part Time Sue SmithSouth Part Time John SmithSouth Part Time Jimmy SmithNorth DivisionAnd the results would be Office OfficeType EmployeeTypeSouth Division Part TimeNorth DivisionI apologize for this error as when I submitted the post I did not have access to the SQL table to capture the table correctly.Can the code be altered to work in this situation? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-28 : 10:46:35
|
Your question is not clear. What does Type Column store Division or Employee Type??? |
|
|
randolphoralph
Starting Member
7 Posts |
Posted - 2012-11-28 : 10:54:30
|
The Type column contains both OfficeType and Employee Type data. The table contains records for Employee's and Office's. The Type column contains the OfficeType if the record contains just an office, and an EmployeeType if the record contains an office and an Employee. |
|
|
randolphoralph
Starting Member
7 Posts |
Posted - 2012-11-28 : 20:59:54
|
can someone tell me if what I am trying to do is even possible? |
|
|
olivelin
Starting Member
1 Post |
Posted - 2012-11-29 : 01:54:23
|
Just try out, you yourself will know whether it would go wrong or right!! |
|
|
|
|
|
|
|