SQL Select employees with work history in multiple states

Question

I need a query that will show only employees who have been paid in more than one state.

The query will pull three columns:

EmployeeID
WorkLocation
LastPayDate

My current, unsuccessful attempt:

Select EmployeeID
    , WorkLocation
    , max(LastPayDate)
from Table 
group by EmployeeID, WorkLocation
having COUNT(distinct WorkLocation) > 1

This query pulls zero records. I know there are employees who have worked in multiple states, however. I am not sure where my logic breaks down.

Any instruction is much appreciated.

`having COUNT(distinct EmployeeID) > 1` should return 0 rows not all rows as each row is grouped by `EmployeeID WorkLocation` and will just have a single value. — Martin Smith, Mar 18 '15 at 21:08

JustinDanielson · Answer 1 · 2015-03-19T00:04:44.420

0

You need to have a count(workLocation) > 1 which indicates that they have worked in more than 1 state. Specify this in the HAVING clause. Since you're only concerned in GROUPS which contain multiple WorkLocations.

If you're trying to check for multiple work locations within a specific year, you will perform that logic in the WHERE clause.

select EmployeeId
from table xyz
//where year(LastPayDate) == 2015
group by EmployeeId 
having count(distinct WorkLocation) > 1

edited Mar 19 '15 at 00:04

answered Mar 18 '15 at 21:06

JustinDanielson

3,155
1
19
26

This will currently return all `EmployeeId` that have more than one row - for any (not null) `WorkLocation` - not necessarily different ones. – Martin Smith Mar 18 '15 at 21:11
Change to `having count(distinct WorkLocation) > 1`, this should work as expected – dnoeth Mar 18 '15 at 22:12
This is good. How would one also include WorkLocation and LastPayDate in the record? @JustinDanielson – Mark Jones Mar 19 '15 at 15:00
When using a group by, you can only include columns if you're using aggregate functions. – JustinDanielson Mar 19 '15 at 18:05

score 0 · Answer 2 · answered Mar 19 '15 at 20:06

Figured it out. I needed to use a subquery. Solution as follows:

Select t.EmployeeID
     , t.WorkLocation
     , t.LastPayDate
From Table t
Where t.EmployeeID in
    (
     Select t2.EmployeeID
     From Table t2
     Group by t2.EmployeeID
     Having count(distinct t2.WorkLocation) > 1
    )
Group by t.EmployeeID, t.WorkLocation
Order by t.EmployeeID

Thanks to everyone for helping.

SQL Select employees with work history in multiple states

2 Answers2