CODEWITHSUNDEEP
sqlsql-server-2008t-sql
MichaelEaton
MichaelEaton

Reputation: 195

select last order date for each customer id

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

Upvotes: 2

Views: 18044

Answers (5)

paparazzo
paparazzo

Reputation: 45106

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]

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

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)

Upvotes: 1

StackTrace
StackTrace

Reputation: 9416

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?

Upvotes: 2

Edwin Stoteler
Edwin Stoteler

Reputation: 1228

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.

Upvotes: 1

Kamal
Kamal

Reputation: 5522

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

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

Upvotes: 3

Related Questions