Joining/Merging where TABLE1.DateTime is between TABLE2.StartDateTime and TABLE2.EndDateTime

[andrewpirie]

I have a table named "SnapshotDateTimes" with a column ReferenceDateTime.

I have another table that holds snapshotted data of a customer record, and a column that identifies the from and two dates that this record is valid (a type 2 SCD). The customer record has the fields Customer_ID, FirstName, LastName, Start_Date, End_Date.

I would like to produce a table with the fields below, by joining SnapshotDateTimes to Customers where SnapshotDateTimes.ReferenceDateTime is between Customers.Start_Date and Customers.End_Date, using PowerQuery filtering.

[SnapshotDateTimes.]ReferenceDateTime, [Customers].Customer_ID, [Customers].FirstName, [Customers.]Surname.

RADACAD.com has an example of what I hope to achieve at Dates Between Merge Join in Power Query, however for me I am concerned that finding all possible dates between my Customers.Start_Date and Customers.End_Date, then joining to SnapshotDateTimes.ReferenceDateTime on individual dates would be poorly performing.

I'm looking for what would be done by the below in T-SQL:

SELECT ...
FROM SnapshotDateTimes
    INNER JOIN Customers
        ON SnapshotDateTimes.ReferenceDateTime BETWEEN SnapshotDateTimes.Start_Date AND SnapshotDateTimes.End_Date

Can anyone point me in the right direction using PowerQuery without needing an in-between "dates" list? I'm imagining I should be able to do something like a foreach over the rows in SnapshotDateTimes, then use Table.SelectRows against the Customers table, filtering on where SnapshotDateTimes.ReferenceDateTime is between Customers.Start_Date and Customers.End_Date.

If this just isn't possible, then it would be awesome to know that too, I may need to rework this to have the limitation of one-snapshot-per-day and use the example linked above.

[v-shex-msft · Accepted solution]

Hi @andrewpirie,

Nope, I don't think it is possible.

These tables has relationship with expand date range which not real existed in table column, you need to create new column to store fully detail range dates to link two tables.

Maybe you can try to use T-sql to create this template link table.

Regards,

Xiaoxin Sheng

[andrewpirie]

Thanks @v-shex-msft - it's great to have an experienced answer on this, I'll try another optimization technique instead of going in circles on this one :).

Andrew