How to make the following code a CTE

[kdjonesmtb2]

Hello

I would like to change the following
Code into CTE so that I can avoid
Using temp tables


drop table #Test1
select
Row_Number() Over(Partition by a.provid order by g.fullname) as Rownumberofaffiliates
, a.affiliationid
, a.effdate
, a.termdate
, g.-- add top 1 for auto script
fullname as site

into #Test1
from dbo.affiliation a
left outer join provider g (nolock) on a.affiliateid = g.provid --Affiliate/Site
where a.provid in ('PRV00002909 ')
and a.affiltype in ('GROUP', 'DIRECT')



select affiliationid as affiliationid from #Test1
where Rownumberofaffiliates = '1'

 

[gmmastros]

Like this:

[!];With Data As
([/!]
select 
Row_Number() Over(Partition by a.provid order by g.fullname) as Rownumberofaffiliates
, a.affiliationid
, a.effdate
, a.termdate
, g.-- add top 1 for auto script
fullname as site 
from dbo.affiliation a
left outer join provider g (nolock) on a.affiliateid = g.provid --Affiliate/Site
where a.provid in ('PRV00002909 ') 
and a.affiltype in ('GROUP', 'DIRECT')
[!])[/!]
select affiliationid as affiliationid from [!]Data[/!]
where Rownumberofaffiliates = '1'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom