I think I have a possible solution (or a least an approach). Note, there is at least one condition you have not accounted for, as I read it:What if the ID is neither RED nor AMBER but some task was completed late? e.g. task 3 was late, but the last task was completed on the 17th which is before the 18th and task 3 was completed before the 18th.declare @t table (ID int,TASK_ID int,TASK_COMPLETEDTS datetime, TASK_DUETS datetime, TASK_INDICATOR varchar(10), ID_INDICATOR varchar(10))insert into @t (ID, TASK_ID, TASK_COMPLETEDTS, TASK_DUETS, TASK_INDICATOR) values(1, 1,'2014-06-09 00:00:00',' 2014-06-11 00:00:00',' GREEN'),(1, 2,'2014-06-13 00:00:00',' 2014-06-14 00:00:00',' AMBER'),(1, 3,'2014-06-17 00:00:00',' 2014-06-16 00:00:00',' RED '),(1, 4,'2014-06-17 00:00:00',' 2014-06-18 00:00:00',' AMBER')select id, TASK_ID, TASK_COMPLETEDTS, TASK_DUETS, TASK_INDICATOR , case when mx.comp > mx.due then 'Red' when TASK_COMPLETEDTS > TASK_DUETS and TASK_COMPLETEDTS <= mx.comp then 'AMBER' when not exists ( select 1 from @t where TASK_COMPLETEDTS > TASK_DUETS ) Then 'Green' END as ID_INDICATOR from @t tcross apply ( select max(task_completedts), max(task_duets) from @t t1 where t.id = t1.id group by id) mx(comp, due)