Join table with one datetime column related to multi datetime values in another column

Question

Table 1:

CustomerID	PurchaseDateTime
C1	C1's PT1
C1	C1's PT2
...	...
C1	C1's PTn
C2	C2's PT1
C2	C2's PT2
...	...

Table 2:

CustomerID	ServiceDateTime
C1	C1's ST1
C1	C1's ST2
...	...
C1	C1's STm
C2	C2's ST1
C2	C2's ST2
...	...

I want to join them to a new table:

CustomerID	PurchaseDateTime	ServiceDateTime
C1	C1's PT1	C1's ST1
C1	C1's PT1	C1's ST2
...	...	...
C1	C1's PT1	C1's STk
C1	C1's PT2	C1's STk+1
C1	C1's PT2	C1's STk+2
...	...	...

One PurchaseDateTime is related to multiple ServiceDateTimes. The earliest related ServiceDateTime is later than the PurchaseDateTime. The last related ServiceDateTime is earlier than the next PurchaseDateTime. All DateTimes in both tables are different.

So for example, for Customer1:

PT1 < ST1 < ST2 < ... < STk < PT2 < STk+1... < STk+j < PT3 ...

I tried:

SELECT 
    t1.CustomerID,
    t1.PurchaseDateTime,
    t2.ServiceDateTime
FROM 
    Table1 t1
JOIN 
    Table2 t2 ON t1.CustomerID = t2.CustomerID
WHERE 
    t2.ServiceDateTime >= t1.PurchaseDateTime
    AND t2.ServiceDateTime <= (
        SELECT MIN(PurchaseDateTime)
        FROM Table1
        WHERE PurchaseDateTime > t1.PurchaseDateTime
        )

However, it does not give the one I would like to have.

How do I join to get the merged table above?

Answer 1

On your correlated subquery you are missing to scope it down to the specific CustomerId.

    SELECT 
    t1.CustomerID,
    t1.PurchaseDateTime,
    t2.ServiceDateTime
FROM 
    Table1 t1
JOIN 
    Table2 t2 ON t1.CustomerID = t2.CustomerID
WHERE 
    t2.ServiceDateTime >= t1.PurchaseDateTime
    AND t2.ServiceDateTime <= (
        SELECT MIN(PurchaseDateTime)
        FROM Table1 
        WHERE PurchaseDateTime > t1.PurchaseDateTime
        AND Table1.CustomerID=t1.CustomerID
    )

Answer 2

with T1Data as (
    select *, lead(t1.PurchaseDateTime) over (partition by t1.CustomerId order by t1.PurchaseDateTime) as nextPDT
    from Tables
)
select *
from T1Data as t1 inner join Table t2
    on  t2.ServiceDateTime > t1.PurchaseDateTime
        and (t2.ServiceDateTime < t1.nextPDT or t1.nextPDT is null);

Presumably it's not possible for both dates to be the same so equality tests aren't necessary. If you do want to allow that possibility, don't test for it at both ends of the range (as with the query in the question,) otherwise the service will become associated with two different purchases. In other words, the intervals shouldn't overlap.