0

How to join two tables with a condition?

TABLE A

from to commission
01/01/2021 12/08/2021 0,2
13/08/2021 31/12/2021 0,3

TABLE B

date client price
07/03/2021 Client A 23 €
08/05/2021 Client B 32 €
14/09/2021 Client C 44 €

EXPECTED OUTPUT

date client price commission
07/03/2021 Client A 23 € 0,2
08/05/2021 Client B 32 € 0,2
14/09/2021 Client C 44 € 0,3

I want to add the COMMISSION that applies to a sell depending on the DATE of the sell. I should join the two tables on "TABLE A"[FROM DATE] < "TABLE B"[DATE] AND "TABLE B"[DATE] < "TABLE A"[TO DATE].

I do not want to join every register in both tables and check that condition after the join. I want to check the condition on the join itself.

Marcus
  • 3,346
  • 1
  • 5
  • 23
mikizas
  • 331
  • 5
  • 16
  • 1
    Please provide some sample data that can be copied along with expected output. – Davide Bacci Sep 05 '22 at 08:29
  • What is stopping you? Where are you stuck? What part are you able to do? [mre] What do "check that condition after the join" & "check the condition on the join itself" mean? WHERE VS ON? Why do you want that? Why do you care? It doesn't matter how ANDed conditions are distributed over ON & WHERE for an inner join, it is trivial for the DBMS to rearrange to the same best implemenation. – philipxy Sep 05 '22 at 08:30
  • 2
    Add it to your question and format it properly. https://meta.stackoverflow.com/questions/277716/how-can-i-create-a-table-in-a-post – Davide Bacci Sep 05 '22 at 09:05
  • @philipxy I usually join the two tables "all to all" (for each line in table A a whole set of table B lines are registered in the output table). After that I check if the condition matches for each line and I filter by the ones that do. That is extremely unefficient, and as far as aI am concerned the DBMS doesnt rearrange that specific case, right? I am able to achieve that behaviour, but I guess that way of operating is not efficient. The problem is that it seems that there is no way to join with a where (or on) clause (in that case the DBMS do rearrange the query, right? Thank you!! – mikizas Sep 05 '22 at 09:06
  • [Inner join vs Where](https://stackoverflow.com/q/121631/3404097) [Execution order of conditions in SQL 'where' clause](https://stackoverflow.com/q/340139/3404097) [SQL - Does the order of WHERE conditions matter?](https://stackoverflow.com/q/3152182/3404097) [Order Of Execution of the SQL query](https://stackoverflow.com/a/4596739/3404097) etc – philipxy Sep 05 '22 at 09:15
  • Please if you are giving code manipulating tables ([mre]) give input tables as initialization code in columns & output as text given. (Rather than using post editor table format.) – philipxy Sep 05 '22 at 09:22

2 Answers2

3

enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDWNzIwMlRQ0lFyzslMzStRcASxjYwVHjWtUYrVASqy0DcwRVfkBGIbG8EVGZroG1iiK3IGsU1MIIpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, client = _t, price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"client", type text}, {"price", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=> Table.SelectRows(TableA, (y)=>  x[date] >= y[from] and x[date] < y[to] )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"commission"}, {"commission"})
in
    #"Expanded Custom"
Davide Bacci
  • 16,647
  • 3
  • 10
  • 36
0
Commison % =
VAR _date =
    SELECTEDVALUE ( 'Table B'[date ] )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'Table A'[commission], 1 ),
        _date >= 'Table A'[from ]
            && _date <= 'Table A'[to ]
    )

commision

Umut K
  • 1,364
  • 12
  • 25