Query with Last Purchase Date by Line Item

Last Post 01 Oct 2007 09:30 PM by DareDelSol. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
DareDelSol
New Member
New Member

--
30 Sep 2007 11:34 AM
I need an effeciant way to return information from the last purchase by the customer for a current order's line items which don't include the current order # to place the information on an Invoice.


Tables:
Orders = OrderNo, OrderDate, CustomerID
OrderLines = OrderNo, ProductID, Qty


Data needed to be returned (restricted by CustomerID) for current order:

OrderNo | ProductID | Qty | "LastPurchaseDate" | "LastPurchaseQty"

Thanks for your time!
DareDelSol
New Member
New Member

--
30 Sep 2007 03:20 PM
There is only the two tables so it would have to be a max(OrderDate) and the corresponding OrderLines.Qty for that OrderID for that Orders.CustomerID.

Thanks for your thoughts.
DareDelSol
New Member
New Member

--
01 Oct 2007 09:30 PM
Is this not going to be possible?
SwePeso
New Member
New Member

--
01 Oct 2007 11:50 PM
Use a derived table where you calculate the latest date for each customer.
Then you join this derived table against your actual tables over MaxDate.
You are not authorized to post a reply.

Acceptable Use Policy