SELECT A.* FROM (SELECT Custid, Max(EndDate) as Expiry FROM Registrations GROUP BY Custid) X
JOIN Registrations A ON A.Custid=X.custid and A.EndDate=X.Expiry;
It shows for each customer his expiry date.
Now I need to add an extra column showing the count per customer (Count(custid)) but all my attempts have been so far unsuccessful.
Help will be much appreciated.
FROM ( SELECT Custid
, MAX(EndDate) as Expiry
, COUNT(*) AS daCount
BY Custid ) AS x
JOIN Registrations AS a
ON a.Custid = x.Custid
AND a.EndDate = x.Expiry