This should be a pretty easy project for someone with a working knowledge of SQL. I need guidance on how to correct a query. If you can tell me how to correct my query, I will gladly consider that completion of the project.
BACKGROUND: We use an inventory system called Fishbowl inventory. I have created a custom sales report using a program called iReport. The query pulls data from a sales order, sales order line, and payments.
When there is one payment applied to the sales order, the report looks fine. However, when multiple payments have been made, the sales order lines are duplicated. If two payments are made, each line is repeated twice. If three payments are made, each line is repeated three times. etc, etc, etc...
I have tried some aggregate functions and the DISTINCT key word, but I cannot get this to work. I believe the issue is in how the payment table is being queried: "......COALESCE([url removed, login to view], 0) AS "POSAmount ....."
I have attached a sample report where the duplication is happening. I have also attached the query text itself.
If you can tell me how to modify the query on my end, I would consider the job complete. I can modify and test here locally, so no configuration is needed on your end.