May 12, 2006 at 7:35 am
Help, I'm stuck with a problem.
I have a customer table and an order table, such as:
TABLE CUSTOMERS
CustomerID....Int
CustomerName...nvarchar(50)
TABLE ORDERS
OrderID......Int
CustomerID....INT
OrderDate.....DateTime
etc....
I need to produce a query that lists all customers, what they last order date was, and their order date previous to that.
I looked at coding in VB, but that involves alot of looping and takes ages to run.
So if I do something like:
SELECT Customer.CustomerName, MAX(OrderDate) FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
I can find their last order date. But I cant get my head around adding a 3rd column to show their orderdate previous to that.
How about a temporary table?
Fine, however I'm still stumped because MAX() isnt going to work. Is there something like SELECT TOP 2 Minimum????
Any thoughts
Regards
Andy
May 12, 2006 at 8:01 am
use a union query
select cutomer_id,max(orderdate) from mytable group by customer_id
UNION
select customer_id,max(orderdate) from mytable where custorders.id not in
(select id from custorders where ..........
MVDBA
May 12, 2006 at 8:46 am
So do I duplicate the first half in the second half?
select cutomer_id,max(orderdate) from mytable group by customer_id
UNION
select customer_id,max(orderdate) from mytable where custorders.id not in
select cutomer_id,max(orderdate) from mytable group by customer_id
I tried this and it just seemed to hang for a long time
May 12, 2006 at 10:18 am
this really depends on your table structure
if you have an id column in the table (as primary key) then it's pretty easy
basically your first query returns the top 1 record
the second half (after the union) returns the top 1 (or max) record from the dataset that doesn't include the fist half....
let me make that more readable
select top 1 employee_no,name from employees
union
select top 1 employee from employees where employee_no not in
(
select top 1 employee_no from employees
)
i'm hoping someone else can explain this better.. it's been a long day
MVDBA
May 15, 2006 at 5:21 am
Andy, here is your code!
------------------------------------------------------------
SELECT Customers.CustomerID,
Customers.CustomerName,
LO.LastOrderDate,
(SELECT MAX(Orders.OrderDate) FROM Orders WHERE Orders.OrderDate < LO.LastOrderDate AND Orders.CustomerID = Customers.CustomerID) PreviousOrderDate
FROM Customers
LEFT JOIN (
SELECT Orders.CustomerID,
MAX(Orders.OrderDate) LastOrderDate
FROM Orders
GROUP BY Orders.CustomerID
) LO ON LO.CustomerID = Customers.CustomerID
------------------------------------------------------------
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply