I'm thinking the net result is something like this:WITH SELECTED_DATA AS (
SELECT C.Cust_MPAN, CONVERT(date, J.PlannedStart, 103) AS [Date], J.[Status], WP.AbortCategory,
ROW_NUMBER() OVER(PARTITION BY C.Cust_MPAN, J.[Status] ORDER BY CONVERT(date, J.PlannedStart, 103) DESC) AS RN
FROM dbo.Jobs AS J
LEFT OUTER JOIN dbo.worksheet_pvt AS WP
ON J.JobId = WP.jobid
LEFT OUTER JOIN dbo.Contacts AS C
ON J.ContactId = C.ContactId
WHERE J.[Status] = N'completed with issues'
AND C.Cust_MPAN = N'2199996080116'
)
SELECT *
FROM SELECTED_DATA AS SD
WHERE SD.RN = 1
ORDER BY SD.Cust_MPAN;
Edit: Query corrected to include DESC for the row number order by.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)