Query to pull back matching data

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 2008 Forums

Transact-SQL (2008)

Query to pull back matching data

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 table

Office OfficeType Employee EmployeeType
East Division
East Division Bill Smith Full Time
East Division Sally Smith Part Time
East Division Bill Smith Maternity Leave
West Field
West Field John Smith Part Time
South Division
South Division Sue Smith Part Time
South Division John Smith Part Time
South Division Jimmy Smith Part Time
North Division

I 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 EmployeeType
South Division Part Time
North Division

Is 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 @offices
values
('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 Divisions
as (
select
Office,
OfficeType,
EmployeeType,
count(EmployeeType) over (partition by Office) CountTypes
from
@offices
where
OfficeType = 'Division'
),
DivSingleType
as (
select
Office,
count (distinct EmployeeType) CountTypes
from
divisions
group by
Office
having
count (distinct EmployeeType) = 1
)
select
Office,
OfficeType,
EmployeeType
from Divisions
where CountTypes = 0

union all

select distinct
a.Office,
a.OfficeType,
a.EmployeeType
from
Divisions a
inner 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,null
from
(
Select Office,OfficeType,Count(*) CNT
from @offices
Where OfficeType = 'Division'
Group by Office,OfficeType
Having Count(*) = 1
)P

Union

Select A.Office,A.OfficeType,A.EmployeeType
from
(
Select Office,OfficeType,EmployeeType,Count(*) CNTA
from @offices
Where OfficeType = 'Division'
Group by Office,OfficeType,EmployeeType
Having Count(*) > 1
)A
inner join
(
Select Office,OfficeType,Count(*) CNTB
from @offices
Where OfficeType = 'Division'
Group by Office,OfficeType
Having 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 follows

Office Type Employee
East Division
East Full Time Bill Smith
East Part Time Sally Smith
East Maternity Leave Sarah Smith
West Field
West Part Time John Smith
South Division
South Part Time Sue Smith
South Part Time John Smith
South Part Time Jimmy Smith
North Division

And the results would be

Office OfficeType EmployeeType
South Division Part Time
North Division

I 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!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources