Search code examples
sqldateconstraintscorrelationdate-comparison

Field correlation with date constraint

We're stuck in a huge challenge here. Let's assume the tables of one db were not properly planned in first hand. That's what it is and I need a solution for that.

There's a table A with 2 fields. Let's think that I have an assistant that supports my job every day, but i just registered when he/she started to assist me. It means the 'Stop Date' (not existent in the table) of each Assistant is the day before the Start Date of the next one.

Assistant | Start Date
James     | 07/01/17
Frank     | 01/03/18
Erika     | 01/06/18

There's a second table B with that registers how many hours my assistant worked:

Date      | Worked Hours
12/31/17  | 7.5    
01/01/18  | 7.5     
01/02/18  | 9        
01/03/18  | 8       
01/04/18  | 9        
01/05/18  | 7.5 
01/06/18  | 9 
01/07/18  | 10

Given the information above, I need to write a SQL to return a table like below, considering the Start Dates of each person:

Assistant | Date     | Worked Hours

Basically I need to correlate somehow the Date and Start Date to return the Assistant, but it involve's date comparisons that I have no idea how to do.

Any ideas how to solve this?

Solution

  • You can use a correlated subquery:

    select b.*,
       (select a.assistant
        from a
        where a.date <= b.date
        order by a.date desc
        fetch first 1 row only
       ) as assistant
from b;

    Note all databases support the ANSI standard fetch first 1 row only, so you may need to use limit or top or whatever is appropriate for your database.