How to link two tables and get results using two dates

Transact-SQL
1

I have two tables in my SQL environment. The first table is called customers and has information about all my customers in my company. The second table is called time frame and is inclusive and has few of my customers.

The customers table has my customers and each customer has an event. Same customer might have multiple events and each event has a start date. This table has about 50k customers

The timeframe table has few of customers and each customer has start and end date. This table has about 9000 customers

What I am trying to do is linking both tables using the customer ID. I only want information about the customers in my timeframe table and not all my customers in my company. And I want to obtain all their events that fall between the program start and end date. Again the events are in my customer tables and each event has a start date so I want to create a logic saying that if the event start date is in between the program start date and program end date (in my timeframe table) return it. below is a picture if my tables and the final result i want:

image
image.png589×639 6.59 KB

2

try

select c.CustomerId, c.EventID, c.EventStartDate, tf.ProgramStartDate, tf.ProgramEndDate
 from Customers c
join TimeFrame tf on c.CustomerId = tf.CustomerId
where EventStartDate between tf.ProgramStartDate and tf.ProgramEndDate
2 Likes
3

Wow! Thanks for the tip!

4

Thanks!