Eric, I don't think the problem has anything to do with your knowledge of SQL. It's not typical for a company to allocate order payments to specific order line items; rather, payments are typically applied to the entire order. Therefore, how could you possibly determine which line item a payment should be applied to...unless I'm missing something.
Dina ----- Original Message ----- From: "Eric Hoffman" <[EMAIL PROTECTED]> To: "CF-Talk" <cf-talk@houseoffusion.com> Sent: Monday, January 24, 2005 7:27 AM Subject: Query Help > I have to make a report based upon this scenario...seems simple, yet I > can't get the numbers right....any pointers? (I have ordered a newer > Advanced SQL book in the meantime!!) > > Need to get orders within date range containing a part number and get > total dollars received in payments for this. > > So I query the orders table....which is related to the order_items > table, where each line item of the order is stored, but does include a > line_total field which did qty * rate charged. On the other hand, the > payments table is related to the orders table by order id, and shows > amount paid...but obviously not breaking out what of the payment applies > to what. > > So how do I query to get the applicable payment amount correct by that > item id? Right now my numbers are inflated because it gets the whole > order payment amount. Recievables higher than sales is great in > fantasyland. Mucho frustrating no doubt. > > Thanks guys. > > Eric > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191537 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
