How can i show the last order and the one before the last?

182 views Asked by At

This is the question:

Write a query that shows for each customer the last order date he placed and the order date before the last View: FirstName, LastName, CustomerID, SalesOrderID, Last Order Date, and one before.

That's what I need to get in the end: enter image description here

This is my code:

WITH CTE
AS
(
SELECT S.SalesOrderID, C.CustomerID, C.PersonID, p.LastName, p.FirstName, S.OrderDate LastOrder,
lag(orderdate,1)OVER(PARTITION By C.personid ORDER BY orderdate ) PreviousOrder
FROM sales.SalesOrderHeader S JOIN Sales.Customer C
ON s.CustomerID =c.CustomerID
JOIN Person.Person p
ON P.BusinessEntityID=C.PersonID
)
SELECT SalesOrderID,CustomerID,LastName , FirstName ,LastOrder,PreviousOrder
FROM CTE E
WHERE LastOrder in (SELECT MAX(LastOrder)
FROM cte i
WHERE c.CustomerID=i.CustomerID
)
0

There are 0 answers