Hi,
Via SQL, I need to extract customers invoices, incl. the invoice lines and the costs for each line. Hence I am joining with InventTrans, to get the individual invoice lines cost so I can calculate the margin and profit.
All is good, I can archieve this. BUT, sometimes there are multiple near-identical rows in the InventTrans-table, and I cannot figure out how to only get the rights ones. This is only relevant for some invoice lines, not all. I have researched and looked for columns and other tables which holds some information telling me how to pinpoint only the relevant rows.
My SQL:
SELECT
cij.invoiceid, cit.LINENUM,cit.ITEMID,cit.INVENTDIMID as [CIT.INVENTDIMEID], it.INVENTDIMID as [IT.INVENTDIMEID], cij.LEDGERVOUCHER, it.VOUCHER, it.VOUCHERPHYSICAL, it.DATEINVENT
FROM CustInvoiceJour cij (NOLOCK)
INNER JOIN custInvoiceTrans cit (NOLOCK) ON (cij.INVOICEID = cit.INVOICEID AND cij.DATAAREAID = cit.DATAAREAID)
INNER JOIN INVENTTRANS it (NOLOCK) ON (it.INVOICEID = cit.INVOICEID AND it.INVENTTRANSID = cit.INVENTTRANSID AND it.INVENTDIMID = cit.INVENTDIMID AND it.DATAAREAID = cit.DATAAREAID)
INNER JOIN INVENTDIM id ON cit.DATAAREAID = id.DATAAREAID AND cit.INVENTDIMID = id.INVENTDIMID
WHERE cit.INVOICEID like '%713620F%' --just for this example, to get a problematic invoice.
Result is like this:
https://ibb.co/GJsD4kF
For 2 of the itemid's there are double lines, which comes from InventTrans. The ONLY difference in the InventTrans-rows are that the VoucherPhysical and DateInvent. I looked into the '1900-01-01' DateInvent value, if this could be the ones to exclude but not - for other invoicelines which only 1 InventTrans row the DateInvent value was '1900-01-01'.
I am not yet fully understanding the whole datamodel and the InventTrans table, so this may be my problem.
I have got the printed invoices and UI-extracted reports, which does not contain the doubled lines.
Please advise if you can, would be much appreciated. I can provide more details and extracts if need be!
