Selecting the last record from each customer before a certain date.

[Jorsha]

Hi, I want to have a query which is attempting to find a list of customers who haven't make a booking for 6 months. Each customer has a separate ID, they make a booking are each stored in a Booking ID table.
I currently have it so that the Departure date is set to <Date()-180, meaning that all bookings before 180 days ago are shown, however I can't work out how to get it so that either Customer ID or Booking ID would only show the last booking made by the customer, which would not show any records from customers who have made bookings in the last 6 months. Would really appreciate help on this.

If I haven't worded this well please let me know and I'll include any other information you need.

Thanks.

 

[ByteMyzer]

You might try something like the following (substitute highlighted text with actual Table/Field names):

SELECT [B][I]Customer.CustomerID[/I][/B], MAX([B][I]Booking.DepartureDate[/I][/B]) AS [B][I]DepartureDate[/I][/B]
FROM [B][I]Customer[/I][/B]
INNER JOIN [B][I]Booking[/I][/B] ON [B][I]Customer.CustomerID[/I][/B] = [B][I]Booking.CustomerID[/I][/B]
GROUP BY [B][I]Customer.CustomerID[/I][/B]
HAVING MAX([B][I]Booking.DepartureDate[/I][/B]) < Date() - 180;