How to solve this SQL query (header and detail)?

Question

I am working with SQL Server 2008 R2.

I have a SQL query question related to header and detail tables. I have a header table where I am storing location & department & week_start_date. I have a detail table where I am storing employee_id & job_code & work_date & hours.

I want to find those employees who are in different headers with same week_start_date but different location and/or department.

Here is the explanation:

---

I have a header table:

CREATE TABLE header (
    header_id bigint not null PRIMARY KEY,
    location_code int not null,
    department_code int not null,
    week_start_date datetime not null )

I have a detail table:

CREATE TABLE detail (
    detail_id bigint not null PRIMARY KEY,
    header_id bigint not null FOREIGN KEY header(header_id),
    employee_id int not null,
    job_code int not null,
    work_date datetime not null,
    hours decimal(8,2) not null )

header table has the unique key as location_code + department_code + week_start_date.

For example this is the data in header table:

header_id=11, location_code=22, department_code=33, week_start_date='2016-02-08'

header_id=12, location_code=22, department_code=39, week_start_date='2016-02-08'

header_id=13, location_code=22, department_code=33, week_start_date='2016-02-15'

header_id=14, location_code=21, department_code=33, week_start_date='2016-02-08'

Each row in header table can have multiple rows in detail table.

detail table has the unique key as header_id + employee_id + job_code + work_date.

For example this is the data in detail table for 1000598 employee_id:

detail_id=101, header_id=11, employee_id=1000598, job_code=77, work_date='2016-02-08', hours=5.00

detail_id=102, header_id=11, employee_id=1000598, job_code=77, work_date='2016-02-09', hours=4.00

detail_id=109, header_id=12, employee_id=1000598, job_code=79, work_date='2016-02-11', hours=4.50

For example this is the data in detail table for 1000599 employee_id:

detail_id=121, header_id=11, employee_id=1000599, job_code=78, work_date='2016-02-10', hours=8.00

detail_id=122, header_id=14, employee_id=1000599, job_code=75, work_date='2016-02-12', hours=3.00

For example this is the data in detail table for 1000600 employee_id:

detail_id=131, header_id=11, employee_id=1000600, job_code=72, work_date='2016-02-11', hours=7.00

detail_id=132, header_id=13, employee_id=1000600, job_code=75, work_date='2016-02-17', hours=3.00

The SQL query should return 1000598 employee_id as 1000598 has data for both department_code=33 and department_code=39 for the same week_start_date='2016-02-08'.

The SQL query should return 1000599 employee_id as 1000599 has data for both location_code=22 and location_code=21 for the same week_start_date='2016-02-08'.

The SQL query should not return 1000600 employee_id.

---

This is the start I have come up with:

select 
   h.employee_id, 
   d.week_start_date
from 
   header h (nolock) 
      inner join detail d (nolock) 
         on h.header_id = d.header_id 
group by 
   h.employee_id, 
   d.week_start_date
order by 
   1, 
   2  

Not much.

Answer 1

I want to find those employees who are in different headers with same week_start_date but different location and/or department.

The query below will return all (employee_id, week_start_date) pairs that have more than 1 location_code or department_code

select d.employee_id, h.week_start_date
from detail d
join header h on h.header_id = d.header_id
group by d.employee_id, h.week_start_date -- employees with same week_start_date
having (
  count(distinct h.location_code) > 1 -- have more than 1 location
  or count(distinct h.department_code) > 1 -- or more than 1 department
)