I have three Temp Tables like this, How I can convert them into CTEs?

select st.*, cl.clientname, stst.studystatusname
#studies
studies st
inner join studiesclients stcl 
on stcl.studysqlid = st.studysqlid , stcl.studyincid = st.studyincid , st.isdeleted = 0x0 , stcl.isdeleted = 0x0 , stcl.sponsorranking = 1
inner join clients cl
on cl.clientsqlid = stcl.clientsqlid , cl.clientincid = stcl.clientincid , cl.isdeleted = 0x0
left join studiesstatuses stst
on stst.studystatussqlid = st.studystatussqlid , stst.studystatusincid = st.studystatusincid , stst.isdeleted = 0x0 
 charindex(cast(left(datepart(yyyy, getdate()), 2)  + substring(st.studycode, 2, 2) nvarchar(30)), @year) > 0
and (charindex(cl.clientcode, @sponsorcode) > 0 or @sponsorcode null)
and  charindex(stst.studystatuscode, @studiesstatuses) > 0 


select distinct ac.activitycode ,ac.activitystartdate
#temp1
activities ac
inner join #studies st on ac.studyincid=st.studyincid
and ac.studysqlid=ac.studysqlid
and ac.isdeleted=0x0
inner join activitiesadditionalfieldsvalues aafv on ac.activityincid = aafv.activityincid
and ac.activitysqlid = aafv.activitysqlid
and ac.isdeleted = 0x0 , aafv.isdeleted = 0x0 , ac.activitycode 's%'
inner join cbovalues cbo on cbo.cbovalueincid = aafv.cborecordincid
and cbo.cbovaluesqlid = aafv.cborecordsqlid
and cbo.isdeleted = 0x0
and cbo.cbovaluename '%first%'
and cbo.cbovaluename '%glp%'


select distinct ac.activitycode ,ac.activityenddate
#temp2
activities ac
inner join #studies st on ac.studyincid=st.studyincid
and ac.studysqlid=ac.studysqlid
and ac.isdeleted=0x0
inner join activitiesadditionalfieldsvalues aafv on ac.activityincid = aafv.activityincid
and ac.activitysqlid = aafv.activitysqlid
and ac.isdeleted = 0x0 , aafv.isdeleted = 0x0 , ac.activitycode 's%'
inner join cbovalues cbo on cbo.cbovalueincid = aafv.cborecordincid
and cbo.cbovaluesqlid = aafv.cborecordsqlid
and cbo.isdeleted = 0x0
and cbo.cbovaluename '%last%'
and cbo.cbovaluename '%glp%'

also want know, moving temp table cte can improve performance allowing sql server push operations related tables/filtering in cte earlier in process resulting in fewer io or cpu cycles.

   

its highly unlikely moving table variable or cte's improve performance. if data size considerable #temp give performance. if small instead of cte use table variable.

why want change cte issue ?

---

cheers,

shashank

please mark reply answer if solved issue or vote helpful if helped other forum members can benefit

my technet wiki articles
mvp

SQL Server  >  SQL Server Integration Services