...in derived and filter for days < 6
SELECT [ID No], THE_MAX, DAYS_DUE
FROM (
--get the max for each ID
SELECT [ID No],
MAX([Rev No]) THE_MAX,
/* correlated subquery to derive the days for this row */
(SELECT DATEDIFF(d,RequiredDate,GetDate()) FROM YOURTABLE B WHERE B.[ID No] = A.[ID No]...