Search code examples
sqlsql-serverdatetimeinner-join

Using the dates in ranges to join and display all dates?

I have two tables, table A and Table B.

Table A includes people's pay hours and calendar dates. Table B has the start dates, end dates, and pay period information.

Sample data from table A:

Calendar_Date   PayHours   Name
11/23/2020      10.0       Scott, Michael
12/02/2020      12.0       Harper, Jim
12/03/2020      12.0       Kim, Maggie
12/10/2020      24.0       Rogers, Steve

Sample data from table B:

Start_date  End_Date    PayPeriod
2020-11-22  2020-12-05  2020-12-wk1
2020-12-06  2020-12-19  2020-12-wk3

What I need is to join these tables so that I could get a result like this.

Calendar_Date   PayHours   Name             PayPeriod
11/23/2020      10.0       Scott, Michael   2020-12-wk1
12/02/2020      12.0       Harper, Jim      2020-12-wk1
12/03/2020      12.0       Kim, Maggie      2020-12-wk1
12/10/2020      24.0       Rogers, Steve    2020-12-wk3

Obviously joining A.Calendar_date = B.Start_date (or B.end_date) is not going to give me the result I need. But is there a way to join these two tables with the dates?

Solution

  • You can join on calendar dates that belong to the pay period range like so:

    select a.*, b.payperiod
from tablea a
inner join tableb b 
    on  c.calendar_date >= b.start_date
    and c.calendar_date <= b.end_date