Sub Queries, Aggregate Functions && Group By Clause

Hi Guys,

I am having trouble with a particular query that is beyond my scope of understanding.

Basically I need to pull sales records based on the following criteria:

I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc..

Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract.

For example, CustomerNumber 123 has recontracted 3 times..

once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15..

So they have a 12 month contract each time.. then come in to recontract it.

So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number.

Currently to pull ALL sales records for all customers, my query is this:


Code:


SELECT xxx.CustomerID AS xxx_CustomerID,
xxx.Invoice AS xxx_Invoice,
yyy.PhoneType AS yyy_PhoneType,
yyy.PlanType AS yyy_PlanType,
yyy.ContractEnds AS yyy_ContractEnds,
yyy.MOB AS yyy_MobileNumber

FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice

WHERE yyy.ContractEnds IS NOT NULL
AND xxx.CustomerID IS NOT NULL



We want to get a list of customers that we can call to recontract, based on the ContractEnd field.

However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date.

So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like:


Code:


SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE xxx.CustomerID='123'
GROUP BY yyy.MOB



Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date)

BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it..

Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query.

Can anyone please help me!

Thanks guys,

Mick