Stored procedure that increase date for bi-weekly schedule

Hi,

I need to add data into a SQL Server table.
The data to be added is a bi-weekly schedule, that needs to be repeated till the end of the year.

During even weeks the schedule looks like: SCHEDULE 1
Monday          Level1
Tuesday         Level2
Wednesday       Level1
Thursday        Level2

Open in new window


During uneven weeks it is: SCHEDULE 2
Monday         Level1
Tuesday        Level2
Thursday       Level2
Friday         Level1
Saturday       Level2

Open in new window


Data must be entered starting from Tuesday januari 1th 2013 (in week one) current date.

Week 1 will be entered as follows:
01-01-2013  Level2
03-01-2013  Level2
04-01-2013  Level1
05-01-2013  Level2

Week 2 will be entered as follows:
07-01-2013  Level1
08-01-2013  Level2
09-01-2013  Level1
10-01-2013  Level2

Week 3 will be entered as follows:
14-01-2013  Level1
15-01-2013  Level2
17-01-2013  Level2
18-01-2013  Level1
19-01-2013  Level2

etc. etc.

How can this be done in an easy way?
Stef MerlijnDeveloperAsked:
Who is Participating?
 
Robert SchuttConnect With a Mentor Software EngineerCommented:
This should get you started:
declare @startdate date
declare @enddate date
declare @weeknum int
declare @weekday int
declare @level int

SET @startdate = '2013-01-01'
SET @enddate = '2013-12-31'
WHILE @startdate <= @enddate
BEGIN
    SET @weeknum = DATEPART(ISO_WEEK, @startdate)
    SET @weekday = (DATEPART(dw, @startdate) + @@DATEFIRST - 2) % 7 + 1
    SET @level = 0
    IF (@weeknum % 2 = 0 and @weekday in (1, 3)) or (@weeknum % 2 = 1 and @weekday in (1, 5))
        SET @level = 1
    IF (@weeknum % 2 = 0 and @weekday in (2, 4)) or (@weeknum % 2 = 1 and @weekday in (2, 4, 6))
        SET @level = 2
    --print '--- DEBUG: ' + convert(varchar, @startdate) + ', ' + convert(varchar, @weeknum % 2) + ', ' + convert(varchar, DATEPART(dw, @startdate)) + '/' + convert(varchar, (DATEPART(dw, @startdate) + @@DATEFIRST - 2) % 7 + 1) + ', ' + convert(varchar, @level)
    IF @level > 0
    BEGIN
        PRINT 'date: ' + convert(varchar, @startdate) + ', level: ' + convert(varchar, @level)
        -- place your INSERT statement here...
    END
    SET @startdate = DATEADD(day, 1, @startdate)
END

Open in new window

output:
date: 2013-01-01, level: 2
date: 2013-01-03, level: 2
date: 2013-01-04, level: 1
date: 2013-01-05, level: 2
date: 2013-01-07, level: 1
date: 2013-01-08, level: 2
date: 2013-01-09, level: 1
date: 2013-01-10, level: 2
date: 2013-01-14, level: 1
date: 2013-01-15, level: 2
date: 2013-01-17, level: 2
date: 2013-01-18, level: 1
date: 2013-01-19, level: 2
date: 2013-01-21, level: 1
date: 2013-01-22, level: 2
date: 2013-01-23, level: 1
date: 2013-01-24, level: 2
date: 2013-01-28, level: 1
date: 2013-01-29, level: 2
date: 2013-01-31, level: 2
...
date: 2013-12-23, level: 1
date: 2013-12-24, level: 2
date: 2013-12-25, level: 1
date: 2013-12-26, level: 2
date: 2013-12-30, level: 1
date: 2013-12-31, level: 2

Open in new window

If you can provide some more details about the table and maybe if you want to create a stored procedure that you can call with a year as parameter for example, I can provide some more detailed code if needed.
0
 
Stef MerlijnDeveloperAuthor Commented:
Perfect, thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.