select o.OpportunityID, s.statusCode, ( select MIN( changeDateTime ) startDate from tbl_SPL_Opportunity_StageChanges sc inner join tbl_SPL_Stages ns on ns.StageID = sc.newStageId where sc.OpportunityId = o.opportunityId and ns.StatusCode = 'A' ) startDate, ( select MAX( changeDateTime ) endDate from tbl_SPL_Opportunity_StageChanges sc inner join tbl_SPL_Stages ns on ns.StageID = sc.newStageId where sc.OpportunityId = o.opportunityId and sc.NewStageId <> sc.PreviousStageId and ns.StatusCode = s.statusCode ) endDate, o.firstActiveDate, o.dateClosed, o.createDate, o.updateDate from tbl_SPL_Opportunities o inner join tbl_SPL_Stages s on s.StageID = o.StageID and s.StatusCode in ('R', 'C' ) inner join tbl_PRD_Products p on p.prodId = o.subjectId and p.ownerId = o.ownerId where o.subjectType = 'PRD' and ( o.firstActiveDate is null or o.dateClosed is null ) update tbl_SPL_Opportunities set , #_f# = where opportunityId = select o.opportunityId, s.statusCode, o.firstActiveDate, o.dateClosed, ( select MIN( sc.changeDateTime ) from tbl_SPL_Opportunity_StageChanges sc inner join tbl_SPL_Stages s on s.StageID = sc.newStageId where sc.OpportunityId = o.opportunityId and ( sc.newStageId = o.StageID or s.StatusCode in ( 'A', 'R', 'C' )) ) newFirstActiveDate, ( select MIN( sc.changeDateTime ) from tbl_SPL_Opportunity_StageChanges sc inner join tbl_SPL_Stages ss on ss.StageID = sc.newStageId where sc.OpportunityId = o.opportunityId and ( sc.newStageId = o.StageID ) ) newDateClosed from tbl_SPL_Opportunities o inner join tbl_SPL_Stages s on s.StageID =o.stageId where s.StatusCode in ( 'C', 'R' ) and ( firstActiveDate is null or DateClosed is null ) update tbl_SPL_Opportunities set firstActiveDate = firstActiveDate, dateClosed = dateClosed where opportunityId = select o.opportunityId, s.statusCode, o.firstActiveDate, MIN( sc.changeDateTime ) changeDateTime from tbl_SPL_Opportunities o inner join tbl_SPL_Stages s on s.StageID =o.stageId inner join tbl_SPL_Opportunity_StageChanges sc on sc.OpportunityId = o.opportunityId and sc.newStageId = o.stageId where s.StatusCode = 'A' and o.firstActiveDate is null group by o.opportunityId, s.statusCode, o.firstActiveDate update tbl_SPL_Opportunities set firstActiveDate = , dateClosed = null where opportunityId =