Solved

Stored procedure that increase date for bi-weekly schedule

Posted on 2014-04-24
2
526 Views
Last Modified: 2014-04-25
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?
0
Comment
Question by:Delphiwizard
2 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 40022119
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
 

Author Closing Comment

by:Delphiwizard
ID: 40022363
Perfect, thank you very much.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question