0

The following is a query to get latest Sales date for a customer.

select pt.CustomerId, pt.SalesDate, pt.*
from dbo.PurchaseTransactions pt
where pt.SalesDate = (
    select max(SalesDate)
    from dbo.PurchaseTransactions subpt
    where pt.CustomerId = subpt.CustomerId
)

Now my next question is, for a given purchase date, a Customer can have multiple purchase order numbers.

How would I acquire the largest order number for a given Maximum Purchase date using subquery? Order numbers can be random, and we just want a random or largest order number line.

We want to utilize subquery. Otherwise, I will have to rewrite as partition order by.

select pt.CustomerId, pt.SalesDate, pt.*
from dbo.PurchaseTransactions pt
where pt.SalesDate = (
    select max(SalesDate)
    from dbo.PurchaseTransactions subpt
    where pt.CustomerId = subpt.CustomerId
) and pt.OrderNumber = ??

1 Answer 1

2

Is this what you are looking for?

WITH MaxSalesDatesPerCustomer
AS
(
SELECT CustomerID, MAX(SalesDate) AS MaxDate
FROM dbo.PurchaseTransactions
GROUP BY CustomerID
)
SELECT MS.CustomerID, MS.MaxDate AS SalesDate, MAX(OrderNumber /*???*/) AS LargestOrderNumberForMaxDatePerCustomer
FROM MaxSalesDatesPerCustomer AS MS
     INNER JOIN
     dbo.PurchaseTransactions AS PT
     ON PT.CustomerID = MS.CustomerID
WHERE MS.MaxDate = PT.SalesDate
GROUP BY MS.CustomerID, MS.MaxDate

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.