R
I thin there can frist select all right next Deadline date, I think you can try this:
select a.reg_id,b.deadline_datefrom requirements a,(select a.id,a.reg_id,a.deadline_datefrom(select a.id,a.reg_id,a.deadline_datefrom deadline a, (select a.* from deadline a,target b where a.Status_ID=b.Status_ID) bwhere a.reg_id=b.reg_id and a.deadline_date>=b.deadline_dateunionselect a.id,a.reg_id,a.deadline_datefrom deadline awhere a.Status_ID not in (select Status_ID from target)) agroup by a.id,a.reg_id,a.deadline_date) bwhere a.reg_id=b.reg_id
good luck.