I have been researching this for the past hour, but cannot seem to make it work. All I need to do is insert these values, but I need to skip insertions that contain are a Saturday or Sunday.
Begin Tran
Declare @VaccID int = 26
Declare @Clinic int = 36
Declare @Enroll int = 30
Declare @SignUp int = 0
Declare @BlnAct int = 1
Declare @BlnDis int = 1
Declare @DateStartTime datetime = '2015-09-16 11:00:00.000'
Declare @DateEndTime datetime = '2015-09-16 14:00:00.000'
Declare @EndDate date = '2015-10-16'
Declare @Days int = DATEDIFF(dd, @DateStartTime, @EndDate)
Select @DateStartTime, @DateEndTime, @Days
Declare @i int = 0
Declare @WeekDay varchar = ''
WHILE @i <= @Days
IF(@WeekDay = 'Monday' OR
@WeekDay = 'Tuesday' OR
@WeekDay = 'Wednesday' OR
@WeekDay = 'Thursday' OR
@WeekDay = 'Friday')
Begin
INSERT INTO dbo.tblSessions
([intVaccID],
[intLocationID],
[dtDateTimeStart],
[dtDateTimeStop],
[intEnrollmentMax],
[intSignedUp],
[blnActive],
[blnDisplay])
Values (@VaccID,
@Clinic,
DATEADD(dd, @i, @DateStartTime),
DATEADD(dd, @i, @DateEndTime),
@Enroll,
@SignUp,
@BlnAct,
@BlnDis)
SELECT @VaccID as intVaccID,
@Clinic as intLocationID,
DATEADD(dd, @i, @DateStartTime) as dtDateTimeStart,
DATEADD(dd, @i, @DateEndTime) as dtDateTimeStop,
@Enroll as intEnrollmentMax,
@SignUp as intSignedUp,
@BlnAct as blnActive,
@BlnDis as blnDisplay
SET @i = @i + 1
SET @WeekDay = DAYOFWEEK(dw, DATEADD(dd, @i, @DateStartTime))
END
Rollback Tran
Commit Tran
It was at least filling in every day of the week, but now seems to be stalling mid query. I know I'm missing something ridiculously small, but I just can't seem to find it. I guess this is my White Whale for the day.
Why don't you use GOTO keyword to skip SATURDAY & Sunday:
Actually your query should be restructred in other way. But for the above scenario you can use this.
WHILE @i< @days
BEGIN
LABEL :
SET @i = @i + 1
SET @WeekDay = DAYOFWEEK(dw, DATEADD(dd, @i, @DateStartTime))
IF(@WeekDay = 'SATURDAY' OR @WeekDay = 'SUNDAY')
GOTO LABEL
END