1. Make sure you are closing the connection after each request made to server. You can check the open connections using below T-SQL.

SELECT

DB_NAME(dbid) as...

Try this - short and sweet

;with mycte as (

select T1.Datecol, ISNULL(T2.Value,0) Val, ROW_NUMBER() OVER(order by T1.datecol) RN

from Dimdate T1 left join TotalsData T2 ON T1.DateCol=T2.DateCol

)

select Datecol,(select top 1 Val...