CTE loop in loop

Question

situation: I got 2 tables. One where there are 2 fields StartDate & EndDate. And one table where there is only one field date. So when you got 3 days between Start- and Enddate. He must insert 3 rows in the new table.

I have next code and it insert perfect my line in the table Availability.

with View_Solidnet_Training as
(
select  cast('2013-04-09' as datetime) DateValue
union all
select DateValue + 1
from View_Solidnet_Training
where DateValue + 1 <= cast('2013-04-11' as datetime)
)
insert into OBJ_Availability  
select 34, DateValue, 'AM', 2, 'Test' from View_Solidnet_Training;

But now, after he inserts the lines in the new table, he stops. But after the loop, he must change the the Start and EndDate again in the new values of the next row in the view: View_Solidnet_Training.

So is there a possible solution, or should I make a new loop where I check if ID of the view is not zero?

Answer 1

As i understand the your question i think you should start from getting the dates into list and then do insert.

Sample:

create table Dates
(
startdate datetime,
endDate datetime
)

insert dates
SELECT '2013-04-06','2013-04-08'

SELECT * from Dates

Declare @date int
Declare  @tbl table 
(
date_ datetime
)

SELECT @date = datediff(day,startDate-1,EndDate) from Dates
SELECT @date

while(@date != 0 )
Begin 

insert into @tbl
SELECT dateadd(day,@date,StartDate-1) from dates

set @date = @date -1

END

/*
--TO-DO
--Update StartDate and EndDate values in table Dates
-- insert YourTable 
-- select date_ from @tbl
*/
SELECT * from @tbl
order by date_