Merge Contents of two tables using a date range in the logic

[Anonymous]

Are  joins involving date ranges possible when merging?

I have two data sources that will help me count the number of business days between a single appointment's [Appt Made Date] and [Appt Visit Date]

Table 1. Scheduled Appts: [Appt Key], [Appt Made Date], [Appt Visit Date]

Table 2. Date Dimension: [Date], [Holiday Flag], [Weekend Flag]

Ideally I want to merge these two data sources like so

Select

S.[Appt Key], Count(D.Date)

FROM Scheduled Appts S

LEFT OUTER JOIN Date Dimension D ON D.[DATE] BETWEEN S.[Appt Made Date] AND S.[Appt Visit Date]

WHERE  [Holiday Flag] IS NULL AND [Weekend Flag] IS NULL

GROUP BY S.[Appt Key]

Is something like that allowed with merging or appending?

[Greg_Deckler · Accepted solution]

Seems like there might be the potential to do some more sophisticated joining with Table.Join but there is no documentation on the joinAlgorithm parameter:

https://msdn.microsoft.com/en-us/library/mt260788.aspx

[v-shex-msft · Accepted solution]

Hi @Anonymous,

smoupre's solution seems well, but if you use the sql server database, you can directly write the T-SQL query at sql statement option:

Notice: "DirectQuery" mode has some limitations with sql query.

Regards,

Xiaoxin Sheng

[Greg_Deckler · Accepted solution]

Seems like there might be the potential to do some more sophisticated joining with Table.Join but there is no documentation on the joinAlgorithm parameter:

https://msdn.microsoft.com/en-us/library/mt260788.aspx