return uses with multiple records from sql server table

I'm using sql server 2008

I have a table called TestEmployees1

It looks like this:

table
This is the script to create the table:

USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestEmployees1](
	[Region] [int] NULL,
	[RegionName] [nvarchar](255) NULL,
	[EmployeeID] [int] NULL,
	[LastName] [nvarchar](255) NULL,
	[FirstName] [nvarchar](255) NULL,
	[MidInit] [nvarchar](255) NULL,
	[HoursWorked] [float] NULL,
	[StatusFlag] [float] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (1, N'New York', 1, N'Johnson', N'Bill', N'A', 55, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (18, N'California', 1, N'Johnson', N'Bill', N'A', 325, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (21, N'Florida', 2, N'Williams', N'John', N' ', 5, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (49, N'Texas', 2, N'Williams', N'John', N' ', 412, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (1, N'New York', 3, N'Smith', N'Ally', N'V', 40.5, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (74, N'Michigan', 3, N'Smith', N'Ally', N'V', 7.5, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (650, N'Oregon', 4, N'Simpson', N'Bart', N'N', 1224, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (650, N'Oregon', 5, N'Simpson', N'Homer', N'R', 1224, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (150, N'Nevada', 6, N'Jackson', N'Joe', N' ', 888, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (22, N'Ohio', 7, N'Kross', N'Chris', N'W', 105, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (150, N'Nevada', 8, N'Smith', N'Michelle', N'R', 744, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (150, N'Nevada', 9, N'Holly', N'Michelle', N'D', 36, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (170, N'Washington', 10, N'Lee', N'Brenda', N' ', 32, 0)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (150, N'Nevada', 11, N'Cowell', N'Simon', N'E', 807, 1)
GO
INSERT [dbo].[TestEmployees1] ([Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag]) VALUES (22, N'Ohio', 12, N'Martin', N'Gus', N'E', 114.5, 0)
GO

Open in new window


I'm trying to write a query to query this table that will do the following.

Return all employees who work in more than 1 region.
So if you look at the table the only employees who work in more than 1 region are
Bill Johnson, John Williams and Ally Smith.

My desired result looks like this:

desired result
I was thinking of using Having Count > 1 but not sure if that is the best to use.

Anyone know a good query to get this result?
LVL 1
maqskywalkerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Jan LouwerensSoftware EngineerCommented:
You could use an analytic function.

SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag] FROM
(
   SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag], COUNT(*) OVER (PARTITION BY [EmployeeID]) AS Total FROM [TestEmployees1]
)
WHERE
   Total > 1
ORDER BY [EmployeeID], [Region]

Open in new window




This would be similar to the HAVING clause you mentioned:
SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag] FROM [TestEmployees1] WHERE
   [EmployeeID] IN
   (
      SELECT [EmployeeID] FROM
      (
         SELECT [EmployeeID], COUNT(*) AS Regions FROM [TestEmployees1]
         GROUP BY [EmployeeID]
         HAVING COUNT(*) > 1
      )
      WHERE
         Total_Regions > 1
   )
ORDER BY [EmployeeID], [Region]

Open in new window

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
maqskywalkerAuthor Commented:
Thanks that worked.

http://sqlfiddle.com/#!18/d85cc/5
0
Jan LouwerensSoftware EngineerCommented:
Ah, it looks like you have to alias the inner query:

SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag] FROM
(
   SELECT [Region], [RegionName], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [StatusFlag], COUNT(*) OVER (PARTITION BY [EmployeeID]) AS Total FROM [TestEmployees1]
) Inner_Query
WHERE
   Total > 1
ORDER BY [EmployeeID], [Region]

Open in new window

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
SQL

From novice to tech pro — start learning today.