I have been brainstorming this situation as well. There is a technique to use two sets of data, which I have outlined in much greater detail in this related post.
Using two GIs as data source | Community
First, you will need a way to join these records in to your query. I’m going to assume that you have that relationship of HistoricalBAccountNumber <> BAccountID.
Then, left join in the Current SO Data table into one (I like to call them) slot, and left join the Historical SO Data into a second. Don’t group by anything, though, you want to preserve the separation of those lists.
Finally, in your results column, use ISNULL([CurrentSOData.Field],[HistoricalSOData.Field]) to essentially merge those two columns together which hold the same information, though separated by data source.
The result set would look something like this,
with Stacked Order # as the ISNULL(Current Order#, Historical Order #):
Customer Number Stacked Order # Current Order# Historical Order #
000001 ACU_ORDER1 ACU_ORDER1 -
000001 ACU_ORDER2 ACU_ORDER1 -
000001 ACU_ORDER3 ACU_ORDER1 -
000001 HIST_ORDER1 - HIST_ORDER1
000001 HIST_ORDER2 - HIST_ORDER1
To be safe, add a condition to exclude any rows where both the historical order # is empty and the slot # is the one for historical records. Otherwise, if there are no historical records, you’ll have an empty record row at the end.
