how to have a double while loop in sql server 2008 -
i'm developing appointment calendar application. still newbie here.
i need in area.
i need have double looping in columns (`calendarid, slot, appointmentdate').
the 'slot' column have value of 1,2,3,4,5,6,7,8 upto 28 repeatedly while calendarid continuously loop 868 value. appointment date have value 1 aug2013 31 aug 2013 (actually i'm planning 1 whole year)
expected result
calendarid | slot | appointmentdate ---------------------------------------------- 1 | 1 | 1 aug 2013 2 | 2 | 1 aug 2013 3 | 3 | 1 aug 2013 4 | 4 | 1 aug 2013 5 | 5 | 1 aug 2013 6 | 6 | 1 aug 2013 7 | 7 | 1 aug 2013 8 |..until 28 | 1 aug 2013 9 | 1 | 2 aug 2013 10 | 2 | 2 aug 2013 11 | 3 | 2 aug 2013 ...until 868 | n | n month 2013 here code try seems i'm far desired output. edited code provided astrand
declare @tblcalendar table(calendarentryid int, slot int, adate varchar(50)) declare @x int, @y int , @d int set @x = 1 set @y = 1 set @d = 1 while @x <= 868 begin while @y <=28 , @d <=31 , @x <= 868 --loop slot column begin insert @tblcalendar (calendarentryid,slot, adate) values (@x, @y,@d +'/aug/2013') set @y = @y + 1 set @x = @x + 1 set @d = @d + 1 end set @y = 1 end select * @tblcalendar sorry trouble of asking , editing original post.
well since it's sql don't think have loop. can generate data recursive cte easily:
with cte ( select 1 calendarid union select calendarid + 1 cte1 calendarid < 100 ) select calendarid, (calendarid - 1) % 8 + 1 cte order calendarid
Comments
Post a Comment