I'm developing an appointment calendar application. Still newbie here.
I need help in this area.
I need to have a double looping in columns (`calendarID, Slot, AppointmentDate').
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)
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 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.
Nested SQL While Loop Syntax Step 1: First, it checks for the condition inside the first While loop. Step 2: It will verify the condition in the Nested SQL While Loop (second While loop). If the result is True, the code inside the second While loop begin… end will execute.
SQL While loop syntax The while loop in SQL begins with the WHILE keyword followed by the condition which returns a Boolean value i.e. True or False. The body of the while loop keeps executing unless the condition returns false. The body of a while loop in SQL starts with a BEGIN block and ends with an END block.
The Common Table Expressions started in SQL Server 2005 and they can be used to replace cursors or the while loop. It is also used for recursive queries and to reference the result table multiple times. As you can see, CTEs is an alternative in many situations to replace the WHILE loop.
OK, this will get you the desired result, but it is questionable. I will try and improve it a bit after that.
DECLARE @tblCalendar TABLE(
CalendarEntryID INT,
Slot INT
)
DECLARE
@x int, @y int
SET @X = 1 SET @y = 1
WHILE @X <= 100
BEGIN
WHILE @Y <=8 AND @X <= 100--LOOP FOR SLOT COLUMN
BEGIN
INSERT INTO @tblCalendar (CalendarEntryID,Slot)
Values (@x, @y)
SET @y = @y + 1
SET @x = @x + 1
end
SET @y = 1
END
SELECT *
FROM @tblCalendar
Another approach would be to make use of an IDENTITY COLUMN
Something like
DECLARE @tblCalendar TABLE(
CalendarEntryID INT IDENTITY(1,1),
Slot INT
)
DECLARE
@x int, @y int
SET @X = 1 SET @y = 1
WHILE @X <= 100
BEGIN
WHILE @Y <=8 AND @X <= 100--LOOP FOR SLOT COLUMN
BEGIN
INSERT INTO @tblCalendar (Slot)
Values (@y)
SET @y = @y + 1
SET @x = @x + 1
end
SET @y = 1
END
SELECT *
FROM @tblCalendar
But personally I would have gone for
DECLARE @Max INT = 100,
@MaxGroup INT = 8
;WITH Val AS (
SELECT 1 CalendarEntryID
UNION ALL
SELECT CalendarEntryID + 1
FROM Val
WHERE CalendarEntryID + 1 <= @Max
)
SELECT CalendarEntryID,
((CalendarEntryID - 1) % @MaxGroup) + 1 Slot
FROM Val
OPTION (MAXRECURSION 0)
well since it's SQL I don't think you have to do loop. You can generate this data with recursive CTE easily:
with cte as (
select 1 as calendarID
union all
select calendarID + 1
from cte1
where calendarID < 100
)
select
CalendarID, (CalendarID - 1) % 8 + 1
from cte
order by CalendarID
sql fiddle demo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With