cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrewpirie Frequent Visitor
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
2 REPLIES 2
Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
andrewpirie Frequent Visitor
Frequent Visitor

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

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 Smiley Happy.

 

Andrew