Database

select compatibility_level from sys.databases where name=db_name()

update outMortgageAsset
set        
	OpeningBalance  = z.OpeningBalance 
	, ClosingBalance  = z.ClosingBalance 
	, AvgBalance      = z.AvgBalance  
from outMortgageAsset b  
join       
	(
		select            
			GroupID      = a.GroupID
			, MonthDate      = k.MonthDate
			, OpeningBalance  = sum(k.OpeningBalance)
			, ClosingBalance  = sum(k.ClosingBalance)
			, AvgBalance      = sum(k.AvgBalance)   
		from EIRGroupAccountsToBeTotaled a   
		join          
		(           
			select                
				monthdate       = b.ForecastMonth
				, accountid       = b.AccountID
				, OpeningBalance  = isnull(b.CurrentBalance, 0.0) 
				, ClosingBalance  = isnull(c.CurrentBalance, 0.0)
				, AvgBalance      = (isnull(c.CurrentBalance, 0.0) 
										+ isnull(b.CurrentBalance, 0.0)) / 2.00000000    
			from EIRAccountBalancePerPeriod b    
			left join EIRAccountBalancePerPeriod c     
				on b.ForecastMonth = c.ForecastMonth - 1     
				and c.AccountID = b.AccountID     
			union all    
			select               
				ForecastMonth - 1 
				, AccountID
				, OpeningBalance  = 0
				, ClosingBalance  = isnull(CurrentBalance, 0.0) 
				, AvgBalance      = (CurrentBalance) / 2.00000000
			from EIRAccountBalancePerPeriod a    
			where ForecastMonth =               
				(
					select min(ForecastMonth) 
					from EIRAccountBalancePerPeriod z 
					where z.AccountID = a.AccountID
				)       
		) k
        on a.accountid = k.accountid
		group by a.GroupID, k.MonthDate
	) z 
    on b.GroupID = z.GroupID      
		and fm = z.MonthDate