0

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
)
Thom A
  • 88,727
  • 11
  • 45
  • 75
EllaYz
  • 1
  • [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Thom A Jul 01 '22 at 10:16
  • As you want the last order *he* placed, does this mean that customers who don't identify as male need to be filtered out? – Thom A Jul 01 '22 at 10:16
  • 1
    You could likely implement a [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) with a `LAG`. – Thom A Jul 01 '22 at 10:20
  • 2
    This post is missing input data, can you update your question accordingly? – lemon Jul 01 '22 at 10:25
  • 1
    `FROM CTE E WHERE ... FROM cte i` Develop GOOD habits. Write consistent code. Spell names consistently. Don't use cryptic aliases. Stop cramming your code together - readable code is less prone to errors and more likely to be understood. – SMor Jul 01 '22 at 11:56

0 Answers0