Using SQL Server CTE to duplicate results of temporary table query -

i've solved problem given work - solved using temporary tables - , i'd prefer solve using ctes if @ possible. appreciate.

the problem determine convention attendance. but, twist is, determine how many attendees have gone convention 3 out of previous 5 years.

this works, code (a little long - sorry ... ):

declare @cnt int = 2006; create table #meetingmatrix (meeting varchar(10), year int); create table #attendees     (year int, id varchar(10)); create table #rollup        (year int, matches int);  -- create table of meetings  insert #meetingmatrix (meeting, [year]) select meeting, cast(year(end_date) int) meet_master  meeting_type='conv'    , (year(end_date) > 2000 , year(end_date) < 2016);   -- create table of has gone on years  insert #attendees ([year], id) select distinct mm.year, orders.st_id orders inner join order_lines on ( orders.order_number = order_lines.order_number ) inner join product on ( order_lines.product_code = product.product_code ) inner join #meetingmatrix mm on product_major=mm.meeting group mm.year, orders.st_id;  -- create yearly results desired while @cnt <= year(getdate()) begin   insert #rollup ([year], matches)   select @cnt, count(*) (     select @cnt 'year', [id], count(*) 'cnt'     #attendees     [year] > (@cnt-5) , [year] <= @cnt     group [id]     having count(*) > 2) r    set @cnt = @cnt + 1; end;  -- display year results select * #rollup  -- clean 

now have start doing same thing via cte - i'm not sure how go through each possible year, , determine results prior 5 years (and count whether individual attendee has gone 3 or more of 5 years):

with meetingmatrix (   select year(end_date) 'year', meeting meet_master    meeting_type='conv'      , (year(end_date) > 2000 , year(end_date) < year(getdate()))    )  , attendeesoverspan (   select distinct product.product_major, mm.year, orders.st_id   orders   inner join order_lines on ( orders.order_number = order_lines.order_number )   inner join product on ( order_lines.product_code = product.product_code )   inner join meetingmatrix mm on product_major=meeting   orders.status not 'c%'      , order_lines.quantity_ordered > 0      , product.category in ( 'rb', 'tk' )     , (lower(product.attributes) <> 'comp' )   group product.product_major, mm.year, orders.st_id)  --, rollupattendees (  --)  select st_id, count(*) 'cnt' attendeesoverspan [year] > 2010 , [year] < 2016 group st_id having count(*) > 2 order cnt desc 

can give me clue how proceed cte path here?

all appreciated.

scott tucson, az

it easy:

1. in cte chain include query years: ,someyears (select distinc [year] cnt ...).
2. insteat use loop, join table.

sample:

select cnt, count(*) (     select cnt 'year', [id], count(*) 'cnt'     attendeesovers inner join someyears on   [year] > (@cnt-5) , [year] <= @cnt     group [id]     having count(*) > 2) ...