select last order date for each customer id

Question

I have a list of customerids, orderids and order dates that I want to use in another query to determine if the customer has ordered again since this date.

Example Data:

CustomerID  OrderID OrderDate
6619    16034   2012-11-15 10:23:02.603
6858    18482   2013-03-25 11:07:14.680
4784    17897   2013-02-20 14:45:43.640
5522    16188   2012-11-22 14:53:49.840
6803    18016   2013-02-28 10:41:16.713

Query:

SELECT    dbo.[Order].CustomerID, dbo.[Order].OrderID, dbo.[Order].OrderDate
FROM         dbo.[Order] INNER JOIN
                  dbo.OrderLine ON dbo.[Order].OrderID = dbo.OrderLine.OrderID 
WHERE     (dbo.OrderLine.ProductID in (42, 44, 45, 46,47,48))

If you need anything else, just ask.

UPDATE::

This query brings back the results as shown above

Need to know if the customer has ordered again since, for any product id after ordering one of the products in the query above..

Mike

I think he wants the last date order that some customer has made — Elior, Apr 12 '13 at 11:40

score 3 · Answer 1 · answered Apr 12 '13 at 11:43

3

If you are only interested in last order date for each customer

select customerid, max(orderdate) from theTable group by customerid;

answered Apr 12 '13 at 11:43

Kamal

5,462
8
45
58

score 2 · Answer 2 · edited May 23 '17 at 11:49

2

In MS SQL you can use TOP 1 for this, you also need to order by your order date column in descending order.

see here SQL Server - How to select the most recent record per user?

edited May 23 '17 at 11:49

Community

1
1

answered Apr 12 '13 at 11:46

StackTrace

9,190
36
114
202

score 1 · Answer 3 · answered Apr 12 '13 at 11:44

SELECT    dbo.[Order].CustomerID, MAX(dbo.[Order].OrderDate)
FROM         dbo.[Order] INNER JOIN
                  dbo.OrderLine ON dbo.[Order].OrderID = dbo.OrderLine.OrderID 
WHERE     (dbo.OrderLine.ProductID in (42, 44, 45, 46,47,48))
GROUP BY dbo.[Order].CustomerID

Gets the latest orderdate of a customer.

score 1 · Answer 4 · answered Apr 12 '13 at 11:54

ROW_NUMBER in a CTE should work:

WITH cte 
     AS (SELECT customerid, 
                orderid, 
                orderdate, 
                rn = Row_number() 
                       OVER( 
                         partition BY customerid 
                         ORDER BY orderdate DESC) 
         FROM   dbo.tblorder 
         WHERE  orderdate >= @orderDate 
                AND customerid = @customerID) 
SELECT customerid, orderid, orderdate
FROM   cte 
WHERE  rn = 1

DEMO

(i've omitted the join since no column from the other table was needed, simply add it)

paparazzo · Answer 5 · 2013-04-12T12:54:05.810

CustomerID and latest OrderDate for customers that have ordered any product after ordering any of a set of products
I suspect they were promotional products

SELECT [Order].[CustomerID], max([Order].[OrderDate])
FROM [Order]
JOIN [Order] as [OrderBase]
  ON [OrderBase].[CustomerID] = [Order].[CustomerID] 
 AND [OrderBase].[OrderDate]  < [Order].[OrderDate]
JOIN [OrderLine] 
  ON [OrderLine].[OrderID] = [OrderBase].[OrderID]
 AND [OrderLine].[ProductID] in (42,44,45,46,47,48)
GROUP BY [Order].[CustomerID]

select last order date for each customer id

5 Answers5