CODEWITHSUNDEEP
sqlsql-server-2008
breezymri
breezymri

Reputation: 4343

Join 3 or more tables with aggregate on one table based on date range

I have 3 tables as following:

Table policyTable.

policy_id employee_id policy_start_date premium 1000001 2000001 2010-03-24 560.1 1000002 2000003 2013-11-01 865.6 1000023 2000201 2012-03-07 435.2 ... ... ...

Table employeeTable.

employee_id department_id job_title_key start_date end_date 2000001 5 1248 2009-05-01 2009-09-23 2000001 5 1248 2009-09-23 2010-02-01 2000001 3 1302 2010-02-01 2011-06-15 2000003 4 2054 2008-03-01 2009-05-12 2000003 4 2054 2009-05-12 2012-01-13 2000003 4 2054 2012-01-13 2014-02-09 2000003 7 5023 2014-02-09 NULL 2000201 2 3125 2010-03-06 2012-01-05 2000201 2 3125 2012-01-05 2013-08-07 2000201 5 1265 2013-08-07 2015-02-20 ...

Table departmentTable:

department_id department_name 2 finance 3 accounting 4 software 5 sales 7 marketing ... ...

My goal is to join these tables to get one record for each policy with the columns policy_id, employee_id, policy_start_date, premium, job_title_key, department_name. My problem right now is with employeeTable, which has multiple records for each employee for different job titles. I only need to get one record for each employee for each policy. The criteria is to choose the one that policy_start_date falls in between the employee start_date and end_date.

So my final desired table is:

policy_id employee_id policy_start_date premium job_title_key department_name 1000001 2000001 2010-03-24 560.1 1302 accounting 1000002 2000003 2013-11-01 865.6 2054 software 1000023 2000201 2012-03-07 435.2 3125 finance ... ... ... ... ... ...

I've tried to use SELECT AGGREGATE GROUP BY with joins, but every column that does not get aggregated needs to go in GROUP BY, which does not work for me here.

Upvotes: 0

Views: 41

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Sql Fiddle Demo

SELECT p.policy_id, e.employee_id, p.policy_start_date, e.job_title_key, d.department_name
FROM policyTable p
INNER JOIN employeeTable e 
   on p.employee_id = e.employee_id
   and p.policy_start_date between e.start_date 
                               and CASE 
                                     WHEN e.end_date IS NULL THEN GETDATE()
                                     ELSE e.end_date
                                   END
INNER JOIN departmentTable d
   on e.department_id = d.department_id

Upvotes: 1

Balinti
Balinti

Reputation: 1534

    with order
    (With Employee_correction as (select 
    employee_id ,   department_id  ,  job_title_key  ,  start_date    , 
    (case when   end_date is null then GETDATE ( ) else end_date end) as end_date
    from employeeTable)
    select policy_id, employee_id ,
    max(policy_start_date) as policy_start_date 
     from policyTable a
    join Employee_correction b on a.employee_is=b.employee_id
    and policy_start_date between start_date and end_date group by policy_id, employee_id )
    select c.policy_id ,c.employee_id ,c.policy_start_date,c.job_title_key,premium,department_name from employeeTable c
    join order on   order.policy_id=c.policy_id
 and order.employee_id=c.employee_id 
 and order.policy_start_date=c.policy_start_date
    join policyTable d on
 order.policy_id=d.policy_id and  
  order.employee_id=d.employee_id 
  and order.policy_start_date=d.policy_start_date
   join departmentTable e on c.department_id=e.department_id

I've made a correction for Employee assuming that when max date is null so the date is today. In addition , to avoid duplicates, I made max aggregation on date.

Upvotes: 0

Related Questions