SQL: Comparing two tables for missing records and then on the date fields

user_629797 0
Viewed 1462

I have two tables as below

work_assignments

emp_id   | start_date  |   End Date
------------------------------------------
  1      | May-10-2017 | May-30-2017
  1      | Jun-05-2017 | null
  2      | May-08-2017 | null

hourly_pay

emp_id   | start_date  |   End Date    |  Rate
-----------------------------------------------
  1      | May-20-2017 | Jun-30-2017   |  75
  1      | Jul-01-2017 | null          |  80

These 2 tables share the emp_id (employee id) foreign key and joining these two I should be able to:

  1. find employee records missing in the hourly_pay table. Given the data here, the query should return emp_id 2 from work_assignments table
  2. find the records where the hourly_pay start_date that are later than the work assignments start_date. Again, given the data here, the query should return emp_id 1 (because work_assignments.start_date has May-10-2017, while the earliest hourly_pay.start_date is on May-20-2017)

I am able to achieve the first part of result using the join query below

select distinct emp_id from work_contracts
left join hourly_pay hr USING(emp_id)
where hr.emp_id is null

I am stuck on the second part where probably I need a correlated subquery to tell the hourly pay table records that did not start before the work_assignments start_date? or is there any other way?

postgresql sql
11 Answers

This hints at a between condition, with some twists, but I've had extremely bad luck using betweens in joins. They appear to perform some form of cross-join on the back and end then filter out the actual join where-clause style. I know that's not very technical, but I've never done a non-equality condition in a join that's turned out well.

So, this may seem counter-intuitive, but I think exploding all date possibilities might actually be your best bet. Without knowing how big your date ranges actually are it's hard to say.

Also, I think this will actually satisfy both conditions in your question at once -- by telling you all work assignments that do not have corresponding pay rates.

Try this against your actual data and see how it works (and how long it takes).

with pay_dates as (
  select
    emp_id, rate,
    generate_series (start_date, coalesce (end_date, current_date), interval '1 day') as pd
  from hourly_pay
),
assignment_dates as (
  select
    emp_id, start_date,
    generate_series (start_date, coalesce (end_date, current_date), interval '1 day') as wd
  from work_assignments
)
select
  emp_id, min (wd)::date as from_date,
  max (wd)::date as thru_date
from
  assignment_dates a
where
  not exists (
    select null
    from pay_dates p
    where p.emp_id = a.emp_id
    and a.wd = p.pd
  )
group by
  emp_id, start_date

The results should be all work assignment ranges with no rates:

emp     from             thru
1    '2017-05-10'    '2017-05-19'
2    '2017-05-08'    '2017-11-14'

The cool thing is it would also remove any overlaps, where a work assignment was partially covered.

-- Edit 3/20/2018 --

Per your request, here is a break-down of what the logic does.

with pay_dates as(
  select
    emp_id, rate,
    generate_series (start_date, coalesce (end_date, current_date), interval '1 day') as pd
  from hourly_pay
)

This takes the hourly_pay data and breaks it into a record for each employee, for each day:

emp_id    rate    pay date
1         75      5/20/17
1         75      5/21/17
1         75      5/22/17
...
1         75      6/30/17
1         80      6/01/17
1         80      6/02/17
...
1         80      today

Next,

[implied "with"]
assignment_dates as (
  select
    emp_id, start_date,
    generate_series (start_date, coalesce (end_date, current_date), interval '1 day') as wd
  from work_assignments
)

Effectively does the same thing for the work assignments table, only preserving the "start date column" in each row.

Then the main query is this:

select
  emp_id, min (wd)::date as from_date,
  max (wd)::date as thru_date
from
  assignment_dates a
where
  not exists (
    select null
    from pay_dates p
    where p.emp_id = a.emp_id
    and a.wd = p.pd
  )
group by
  emp_id, start_date

Which draws from the two queries above. The important part is the anti-join:

not exists (
  select null
  from pay_dates p
  where p.emp_id = a.emp_id
  and a.wd = p.pd
)

That identifies every work assignment where there is no corresponding record for that employee, for that day.

So in essence, the query takes the data ranges from both tables, comes up with every possible date combination and then does an anti-join to see where they don't match.

While it seems counterintuitive, to take a single record and blow it up into multiple records, two things to consider:

  1. Dates are very bounded creatures -- even in 10 years worth of data that only constitutes 4,000 or so records, which isn't much to a database, even when multiplied by an employee database. Your time frame looks much less than that.

  2. I've had very, VERY bad luck using joins other than =, for example between or >. It seems in the background it does cartesians and then filters the results. By comparison, exploding the ranges at least gives you some control over how much data explosion occurs.

For grins, I did it with your sample data above and came up with this, which actually looks accurate:

1   '2017-05-10'    '2017-05-19'
2   '2017-05-08'    '2018-03-20'

Let me know if any of that is unclear.

user_10018840
Related