I am trying to create an auxiliary table from two other tables with an "IntervalMatch" serving as connecting rule. But once I join those two tables I cannot dump its contents inside a third table (since I cannot use two "Resident" tables), so I tried to load the other fields from the "Resident" table in the "Inner Join IntervalMatch" but it generates a cartesian product.
Which would be the best way to generate this third table keeping the interval match logic?
// Loading columns from "SalesOrderHeader"
SalesOrderID as Special.SaleID,
[OrderDate] as Special.OrderDate,
[TerritoryID] as Special.TerritoryID1,
[OnlineOrderFlag] as Special.Online
// Loading columns from "SalesTerritoryHistory"
[TerritoryID] as Special.TerritoryID,
[BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,
[SalesTerritoryHistory.StartDate] as Special.StartDate,
Aside from deleting the comment: "// Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"" I cant find any difference to my code. I need the columns [Special.Online], [Special.OrderDate] and [Special.SaleID] in the table too.
Thanks for clarifying. Unfortunately the problem persists, for each SaleID it has a bunch of TerritoryID. Like if it is not respecting the relation of the "Special.TerritoryID" from [SalesOrderHeader] with the "Special.TerritoryID" from [SalesTerritoryHistory] and it just take all salesperson working in that period regardless of the region.