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 2008 Forums
 Transact-SQL (2008)
 Query to pull back matching data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

randolphoralph
Starting Member

7 Posts

Posted - 11/27/2012 :  18:52:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 11/27/2012 :  20:30:56  Show Profile  Reply with Quote
I gotta believe that there is an simpler way of doing this but...
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



=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!
Go to Top of Page

randolphoralph
Starting Member

7 Posts

Posted - 11/27/2012 :  21:22:20  Show Profile  Reply with Quote
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.
Go to Top of Page

randolphoralph
Starting Member

7 Posts

Posted - 11/27/2012 :  21:30:12  Show Profile  Reply with Quote
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!
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/27/2012 :  21:57:49  Show Profile  Reply with Quote
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
Go to Top of Page

randolphoralph
Starting Member

7 Posts

Posted - 11/28/2012 :  10:42:17  Show Profile  Reply with Quote
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?


Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/28/2012 :  10:46:35  Show Profile  Reply with Quote
Your question is not clear. What does Type Column store Division or Employee Type???
Go to Top of Page

randolphoralph
Starting Member

7 Posts

Posted - 11/28/2012 :  10:54:30  Show Profile  Reply with Quote
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.
Go to Top of Page

randolphoralph
Starting Member

7 Posts

Posted - 11/28/2012 :  20:59:54  Show Profile  Reply with Quote
can someone tell me if what I am trying to do is even possible?
Go to Top of Page

olivelin
Starting Member

United Kingdom
1 Posts

Posted - 11/29/2012 :  01:54:23  Show Profile  Reply with Quote
Just try out, you yourself will know whether it would go wrong or right!!


Edited by - olivelin on 11/29/2012 01:56:41
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.09 seconds. Powered By: Snitz Forums 2000