SQLTeam.com | Weblogs | Forums

I would like a result set of staff with multiple offices

In this query select last_name, first_name, job_title, site_name from staff_site_link_expanded_view order by last_name, first_name

It lists some of the staff more than once as they have multuple offices (site_name).

I would like a list of which staff has this, more than one site_name.
I would like just their last_name, and first_name who has multi site_name

For example this staff:

|ANDREWS|GARY|Clinician |Wood Admin Office|

|ANDREWS|GARY|Clinician |Genola Admin Office-East|
|ANDREWS|GARY|Clinician |Genola Admin Office-West|
|ANDREWS|GARY|Clinician |Bevins Street Admin Office|

What is the unique employee identifier

There could be more than one person with same name

select
columns_you_want
, count( distinct site)
group by
columns_you_want
having count(distinct site) > 1

1 Like

There could be 2 people with same name in same department

1 Like

Yup

have to code for it if it comes up in data

thanks for pointing Yosiasz

Op needs to provide a unique identifier right?

Yup

i am guessing from his answer .. that he is not PARTUICULAR about it

in our system it is not right now the same name.

yes we can have staff_id column that is a fine idea.

Right now yes but...