You’re offline. This is a read only version of the page.
7
I am trying to create a view out of the GP database that I can use to import into a data model for Power BI for reporting on inventory layers. My background is in working with relational databases, not in GP or accounting.
I want the QTYRECVD and QTYSOLD out of the work table (IV10200), but I also want the DOCDATE (for the date sold) out of the detail (IV10201), so I can determine when each layer was sold. So far, every way I can think of to join these two tables in the T-SQL for the view results in duplication. For example, if Receipt 1 brought in 20 items on 1/1/2018, and these items were sold on two different dates--say, 2 items on 2/1/2018 and 10 on 3/1/2018--then I should have received a total of 20, sold a total of 12, and have 8 on hand (assuming no adjustments, which is why I get the QTYSOLD out of the header, which will include adjustments and should make my on-hand number accurate). The trouble is, I haven't found a way to join these two tables that won't result either in two copies of the header (one for each of the two sales in the detail table), which makes it look as though I've received 40 instead of 20, or (if, for example, the two sales happened to be on the same date) that is falsely eliminating some of the detail records, so it looks like I received 20 but only sold 2.
My current join:
FROM dbo.IV10200 AS PRH -- Purchase Receipts Header
LEFT OUTER JOIN dbo.IV10201 AS PRD -- Purchase Receipts Detail
ON PRH.ITEMNMBR = PRD.ITEMNMBR
AND PRH.TRXLOCTN = PRD.TRXLOCTN
AND PRH.RCTSEQNM = PRD.SRCRCTSEQNM
AND (PRH.UNITCOST = PRD.UNITCOST OR PRD.UNITCOST = 0)
AND PRH.QTYSOLD <> 0
Does anyone have any suggestions for how to better join these tables so I see what I want without counting the numbers in the header record twice?
Thank you.
*This post is locked for comments
I have the same question (0)
Share
Report
All responses (
Answers (
