Last Order Placed

Question

Last Order Placed

b_boy

SSCertifiable

Points: 5145
More actions
August 14, 2008 at 7:23 am

#211241

Hello
Am using the following query:
SELECT Customers.Country, Customers.County, Customers.[e-mail], CONVERT(varchar(20), Customers.Entrydate, 102) AS EntryDate,
Customers.NumOrders, (OrderHeader.OrderDate)
FROM Customers INNER JOIN
OrderHeader ON Customers.CustomerID = OrderHeader.CustomerID
WHERE (Customers.County IN ('Poland', 'Austria', 'Belgium', 'Cyprus', 'Czech Republic', 'Denmark', 'Estonia', 'Finland', 'France', 'Netherlands',
'Irish Republic', 'Italy', 'Latvia', 'Luxembourg', 'Bulgaria', 'Poland', 'Portugal', 'Slovakia', 'Slovenia', 'Spain', 'Sweden', 'Germany', 'Romania', 'US',
'USA', 'United Kingdom'))
ORDER BYCustomers.County
---
And I want to also know when each customer last placed an order ( the lasttime they placed an order)
will i have to use the MAX keyword?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 1

is this what you're asking for?

SELECT Customers.Country, Customers.County, Customers.[e-mail], CONVERT(varchar(20), Customers.Entrydate, 102) AS EntryDate,

Customers.NumOrders, MAX(OrderHeader.OrderDate)

FROM Customers INNER JOIN

OrderHeader ON Customers.CustomerID = OrderHeader.CustomerID

WHERE (Customers.County IN ('Poland', 'Austria', 'Belgium', 'Cyprus', 'Czech Republic', 'Denmark', 'Estonia', 'Finland', 'France', 'Netherlands',

'Irish Republic', 'Italy', 'Latvia', 'Luxembourg', 'Bulgaria', 'Poland', 'Portugal', 'Slovakia', 'Slovenia', 'Spain', 'Sweden', 'Germany', 'Romania', 'US',

'USA', 'United Kingdom'))

GROUP BY Customers.Country, Customers.County, Customers.[e-mail], CONVERT(varchar(20), Customers.Entrydate, 102) AS EntryDate,

Customers.NumOrders

ORDER BY Customers.County

b_boy SSCertifiable Points: 5145 More actions · Answer 2

I want know the last time a customer placed an order in addtion to the information required

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 3

did the modified query not return the data you're looking for? by using max(orderdate) and GROUPing BY the fields in your SELECT clause, you should get one row per unique set of fields along with the most recent orderdate.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

If the orderdate is the correct time item, the MAX() should give you what you need.

If it doesn't, please show some data and explain what's not right.

b_boy SSCertifiable Points: 5145 More actions · Answer 5

I got the right result, my mind was going towards an inequlity or something syntax to be needed - Thanks