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 benefitmy technet wiki articles
mvp
SQL Server > SQL Server Integration Services
Comments
Post a Comment