use EnVisage
go

select s.id, s.Name, MAX(sd.quantity) as maxquantity
into #tmp
from ScenarioDetail sd
inner join Scenario s on s.Id = sd.ParentID
where s.Type = 13
group by s.Id, s.Name

declare tms cursor for select t.id, t.Name, t.PlannedCapacityScenarioId from Team t 
left join ScenarioDetail sd on sd.ParentID = t.PlannedCapacityScenarioId
group by t.id, t.Name, t.PlannedCapacityScenarioId
having  MAX(sd.quantity) is null or MAX(sd.quantity) < 1
order by t.Name
declare @teamId uniqueidentifier
declare @teamPlannedScenarioId uniqueidentifier
declare @teamName nvarchar(500)
declare @maxq decimal
declare @newScenId uniqueidentifier
open tms
fetch next from tms into @teamId, @teamName, @teamPlannedScenarioId
while @@FETCH_STATUS = 0
begin
	print 'begin team ' + @teamName
	set @maxq = null
	select @maxq = maxquantity, @newScenId = id from #tmp where name like @teamName + ' Planned Capacity' and id <> @teamPlannedScenarioId order by maxquantity desc
	if @maxq is not null
	begin
		print 'found scenario with maximal quantity = ' + cast(@maxq as nvarchar(50)) + ', applying this scenario'
		update Team set PlannedCapacityScenarioId = @newScenId where id = @teamId
		exec sp_DeleteScenario @teamPlannedScenarioId
	end
	else
		print 'found no other scenarios for this team - leaving zero scenario'
	print '-------------------------'
	fetch next from tms into @teamId, @teamName, @teamPlannedScenarioId
end
close tms
deallocate tms

drop table #tmp