declare @weeks int
set @weeks = 3
;with mycte as (
select min([table_date]) as startPoint, max([table_date]) as endPoint, [id]
from [table]
group by id
), mycte2 as (
select startPoint, endPoint, id
from mycte
union all select DATEADD(week,@weeks,startPoint), endPoint, id
from mycte2
where DATEADD(week,@weeks,startPoint) <= endPoint
)
select * from [table] t
inner join mycte2 m on t.id = m.id and t.[table_date] = m.date
There is a lot going on here. Here are some things to look into
Using Common Table Expressions[
^]
Recursive Queries Using Common Table Expressions[
^]
UPDATE: Whoops. "Union
all" is the correct syntax