TSQL - How to extract column with the Minimum and Maximum Value in another column

Question

eg.

I have 2 tables Customers and Orders

Customers has columns CustomerID, Name

Orders has columns OrderID, OrderedOn

where OrderedOn is a DateTime

Now I want a query which will give me the

CustomerID OrderID and OrderTally

where OrderTally = 'Initial' for the first order OrderTally = 'InMiddle' for everything in the middle and OrderTally = 'Final' if its the last order and was 30 days ago,

I am trying to create a Case statement for OrderTally

and struggling

How do I check if the OrderID is the first or last or in the middle

 -- The First Order
 CASE WHEN OrderID IN (...)
 THEN 'Initial'
 -- The Last Order
 WHEN OrderID IN (...)
 THEN 'Final'
 ELSE
 'InTheMiddle'
 END

I was thinking of writing ranking statements and then check if it rank is one that's the first one and if rank = total count of all orders then last... but this seems a little complicated.

Is there an easy way of doing this?

Answer 1

How about this:

SELECT o.CustomerId, o.OrderId, 
    CASE 
        WHEN o.OrderedOn = o2.FirstOrderDate THEN 'Initial'
        WHEN o.OrderedOn = o2.LastOrderDate THEN 'Final'
        ELSE 'InTheMiddle'
    END AS OrderTally
FROM [Orders] o
    JOIN
    (
        SELECT CustomerId, MIN(OrderedOn) AS FirstOrderDate, MAX(OrderedOn) AS LastOrderDate
        FROM [Orders]
        GROUP BY CustomerId
    ) o2 ON o.CustomerId = o2.CustomerId

I've made the assumption that a customer won't have 2 orders with the same OrderOn date/time - if they do, this would possibly result in 2 orders being classed as "Initial" or "Final". But it seemed like a reasonable assumption.