I have two tables with financial information. One containing paid capital and paid interest (table 1) and one containing commission (table 2). The problem is that in table 1 paid capital and paid interest are on different rows so each case is duplicated when there is both paid on capital and interest, and in table 2 the commission is on case basis - not on capital/interest basis.
When I then join the two tables together to view paid capital, paid interest and debited commission on case level the commission is duplicated due to data structure in table 1, which it shouldn't be. I can't use the "sum(distinct..." on the commission summery as there can be a lot of equal amounts that must be summerized. I'm not strong in aggr functions.
You could join those 2 separte rows in one row in the first table (if that doesnt cause problem in some other areas, like other values from those fields that have to be kept). First load table X from table 1 using condition that paid interest must be null. Then join paid capital also from table 1 to newly created table X. This should get you paid capital and paid interest on the same row.
Or, maybe better, you could "flag" let's say paid capital row and join commsion to table 1 only on rows which are flagged. That way commision would be joined only on paid capital rows.
