While loop?

External Since: Apr 26, 2007 Posts: 5

So I am just starting to learn how to use Database. I need the help of a SQL
guru! I have these three tables:
CREATE TABLE SB3_ScheduleChange
(
emp_num EmpNumType:nvarchar(7) NOT NULL PRIMARY KEY,
Monday char(35) NULL,
Tuesday char(35) NULL,
Wednesday char(35) NULL,
Thursday char(35) NULL,
Friday char(35) NULL,
Saturday char(35) NULL,
Sunday char(35) NULL,
Name char(35) NOT NULL;
)

CREATE TABLE SB3_ScheduleChange
(
emp_num EmpNumType:nvarchar(7) NOT NULL PRIMARY KEY,
sched_type_id nt, NOT NULL,
start_date DateType:datetime NOT NULL,
end_date DateType:datetime NOT NULL,
comments varchar(8000) NULL,
)

CREATE TABLE SB3_ScheduleType
(
sched_type_id int, NOT NULL PRIMARY KEY,
reason char(20) NULL,

)

I am trying to come up with a query that will iterate through each employee
with the emp_num primary key in the emp_shcedule table. I would like the
iteration to check if the employee has an exception or "change" from their
regular default schedule in the table emp_schedule checking it against the
"SB3_ScheduleChange" table. And if the query finds a difference in schedule
to update or insert it into that employees default schedule until the change
is over on a certain date. My problem is I am new to SQL and I dont know how
to do this. Do I need a Calendar table? Use a while loop? Any examples,
help, or suggestions would be very much appreciated. Not that this will help
but here are some queries I have come up with. Just dont know how to tie it
all together.

This would list name, emp_num, sched_type_id, for each employee who has
A schedule type difference. Query shows every employee with a schedule type
difference.

SELECT es.Name, es.emp_num, sc.sched_type_id
FROM emp_schedule AS es JOIN SB3_ScheduleChange AS sc
ON(es.emp_num = sc.emp_num)

List name, emp_num of employees that are not assigned to any schedule
change. Query shows every employee without a schedule type difference.

SELECT name, emp_num
FROM emp_schedule AS es
WHERE NOT EXISTS
(SELECT emp_num
FROM SB3_ScheduleChange AS sc
WHERE es.emp_num = sc.emp_num);

And if necessary a Calendar CREATE TABLE. But once again I am not sure.
CREATE TABLE dbo.SB3_Calender
(
ActualDate DATETIME NOT NULL PRIMARY KEY,
MonthName CHAR(15) NULL,
DayNumber INT NULL,
YearNumber INT NULL,
DayOfWeek CHAR(15) NULL
CHECK (DayOfWeek IN ('Sunday', 'Monday', 'Tuesday',
'Wednesday','Thursday','Friday','Saturday')),
DayType CHAR(15) NULL
CHECK ( DayType IN ('Business','Weekend','Holiday')),
)

GO

SET NOCOUNT ON
DECLARE @Counter INT
DECLARE @ActualDate DATETIME
DECLARE @FirstDate DATETIME
SET @Counter = 1
SET @FirstDate = '1/1/2006'
SET @ActualDate = @FirstDate
WHILE @Counter < 1096
BEGIN
INSERT INTO Calender (ActualDate)
values(@ActualDate)
SET @ActualDate = DATEADD(day, @Counter, @FirstDate)
SET @Coutner = @Counter + 1
END

GO

UPDATE Calender
SET DayOfWeek = DateName(DW, ActualDate)

GO

UPDATE Calender
SET DayNumber = DateName(DD,ActualDate)

GO

UPDATE Calender
SET MonthName = DateName(MM,ActualDate)

GO

UPDATE Calender
SET YearNumber = DateName(YY,ActualDate)

GO

UPDATE Calender
SET DayType = 'Business'
WHERE DayOfWeek <> 'Saturday' AND DayOfWeek <> 'Sunday'

GO

UPDATE Calender
SET DayType = 'Weekend'
WHERE DayOfWeek = 'Saturday' OR DayOfWeek = 'Sunday'

GO

UPDATE Calender
SET DayType = 'Holiday'
WHERE (MonthName ='January' AND DayNumber = 1 ) OR
(MonthName ='July' AND DayNumber = 4) OR
(MonthName ='December' AND DayNumber = 25)

GO

Thank you in advance!

External Since: Oct 08, 2006 Posts: 9

Lookup "DECLARE CURSOR" in SQL Books Online.