title: How to solve this SQL query (header and detail)?
body: <p>I am working with SQL Server 2008 R2.</p>
  
  <p>I have a SQL query question related to header and detail tables. I have a header table where I am storing location &amp; department &amp; week_start_date. I have a detail table where I am storing employee_id &amp; job_code &amp; work_date &amp; hours. </p>
  
  <p>I want to find those employees who are in different headers with same week_start_date but different location and/or department. </p>
  
  <p>Here is the explanation:</p>
  
  <hr>
  
  <p>I have a <strong>header</strong> table:</p>
  
  <pre><code>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 )
  </code></pre>
  
  <p>I have a <strong>detail</strong> table:</p>
  
  <pre><code>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 )
  </code></pre>
  
  <p><strong>header</strong> table has the unique key as location_code + department_code + week_start_date. </p>
  
  <p>For example this is the data in <strong>header</strong> table:</p>
  
  <blockquote>
    <p>header_id=11, location_code=22, department_code=33,
    week_start_date='2016-02-08'</p>
    
    <p>header_id=12, location_code=22, department_code=39,
    week_start_date='2016-02-08'</p>
    
    <p>header_id=13, location_code=22, department_code=33,
    week_start_date='2016-02-15'</p>
    
    <p>header_id=14, location_code=21, department_code=33,
    week_start_date='2016-02-08'</p>
  </blockquote>
  
  <p>Each row in <strong>header</strong> table can have multiple rows in <strong>detail</strong> table. </p>
  
  <p><strong>detail</strong> table has the unique key as header_id + employee_id + job_code + work_date.</p>
  
  <p>For example this is the data in <strong>detail</strong> table for 1000598 employee_id:</p>
  
  <blockquote>
    <p>detail_id=101, header_id=11, employee_id=1000598, job_code=77,
    work_date='2016-02-08', hours=5.00</p>
    
    <p>detail_id=102, header_id=11, employee_id=1000598, job_code=77,
    work_date='2016-02-09', hours=4.00</p>
    
    <p>detail_id=109, header_id=12, employee_id=1000598, job_code=79,
    work_date='2016-02-11', hours=4.50</p>
  </blockquote>
  
  <p>For example this is the data in <strong>detail</strong> table for 1000599 employee_id:</p>
  
  <blockquote>
    <p>detail_id=121, header_id=11, employee_id=1000599, job_code=78,
    work_date='2016-02-10', hours=8.00</p>
    
    <p>detail_id=122, header_id=14, employee_id=1000599, job_code=75,
    work_date='2016-02-12', hours=3.00</p>
  </blockquote>
  
  <p>For example this is the data in <strong>detail</strong> table for 1000600 employee_id:</p>
  
  <blockquote>
    <p>detail_id=131, header_id=11, employee_id=1000600, job_code=72,
    work_date='2016-02-11', hours=7.00</p>
    
    <p>detail_id=132, header_id=13, employee_id=1000600, job_code=75,
    work_date='2016-02-17', hours=3.00</p>
  </blockquote>
  
  <p>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'.</p>
  
  <p>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'.</p>
  
  <p>The SQL query should not return 1000600 employee_id.</p>
  
  <hr>
  
  <p>This is the start I have come up with:</p>
  
  <pre><code>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  
  </code></pre>
  
  <p>Not much. </p>