如何在SQL Server 2008中进行两次while循环 [英] how to have a double while loop in sql server 2008

问题描述

我正在开发约会日历应用程序.这里还是新手.

I'm developing an appointment calendar application. Still newbie here.

在这方面我需要帮助.

我需要在各列("calendarID，Slot，AppointmentDate")中进行两次循环.

I need to have a double looping in columns (`calendarID, Slot, AppointmentDate').

"slot"列的值将重复1、2、3、4、5、6、7、8最多重复28，而calendarID将连续循环到868值. 约会日期的值是从2013年8月1日到2013年8月31日(实际上我打算整整一年)

The 'slot' column will have a value of 1,2,3,4,5,6,7,8 upto 28 repeatedly while the calendarID will continuously loop to 868 value. The Appointment date will have the value from 1 Aug2013 to 31 Aug 2013 (actually I'm planning to do this for 1 whole year)

预期结果

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  

这是我尝试的代码，似乎与所需的输出相距甚远. 我编辑了Astrand提供的代码

Here is my code that I try seems I'm very far from my desired output. I edited the code provided by 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 AND @d <=31 AND @X <= 868 --LOOP FOR SLOT COLUMN
        BEGIN

        INSERT INTO @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  *
FROM    @tblCalendar

很抱歉询问和编辑我的原始帖子.

sorry for the trouble of asking and editing my original post.

推荐答案

这是我帖子的答案，以防万一将来像我这样的人也有同样的问题.

Here is the answer to my post, just in case in the future someone like me will have the same question.

DECLARE
@x int, @y int, @d date, @i date, @status Nvarchar(50)
SET @X = 1 
SET @y = 1
SET @d = DATEADD (dd, 1 , '31/Jul/2013') -- '2013/07/01' --default date to August
SET @status = 'Available'

WHILE @X <= 868

BEGIN 

WHILE @Y <=28 AND @X <= 868--LOOP FOR SLOT COLUMN
        BEGIN
            INSERT INTO  tblCalendar (CalendarEntryID,Slot,Date,Status)
            Values (@x, @y,@d,@status)
            SET @x = @x + 1
            SET @y = @y + 1
            SET @i = @d
        END
SET @d = DATEADD (dd, 1 , @i) -- THIS WILL INCREMENT THE DATE ACCORDING TO SLOT
SET @y = 1  
END
SELECT  *
FROM    tblCalendar

这篇关于如何在SQL Server 2008中进行两次while循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！